Iinserting a Excel Report at end or Cmm program

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Iinserting a Excel Report at end or Cmm program

    I have a customer whom would like to insert a custom excel document at the end of the program that will import the cmm results from every run of that part into one excel document .They used to have it working when they ran older version pcdmis but that was some one elses custom code in order for it to work back then..Now with the add on for 2019R1 it can be done but how.I can not see how they linked the last XML output .xml file to this report for every new run

    any help would be apreciated






    I have attached a copy of their old excel output report. Excell report output Capture.JPG



    and the original excel template that they supposedly used Original Excell report Capture.JPG
    Michael A Wildschutz Sr
    I Walk on The WildSide
    "To Each is Own"

  • #2
    Jeffman or Anders any Ideas on this new excel report feature
    Michael A Wildschutz Sr
    I Walk on The WildSide
    "To Each is Own"

    Comment


    • #3
      ASSIGN/C_PATH="C:\PRODUCTION\CUSTOMER\PART_NUMBER\LOT_OUT PUT.CSV"
      C_FILE =FILE/OPEN,C_PATH,APPEND
      FILE/WRITELINE,C_FILE,date,operator,SN,measured1,2,3,4, 5,6,7,8, etc.
      FILE/CLOSE,C_FILE,KEEP

      That's the oldschool way...

      Comment


      • #4
        Don Rugerri any ideas on this new excel report
        Michael A Wildschutz Sr
        I Walk on The WildSide
        "To Each is Own"

        Comment


        • #5
          It can be done using file IO!

          EXCEL_FORM1 =EXCELFORMREPORT/TEMPLATE=Blank_meggitt_template2, TYPE=XLSX
          RESULT FOLDER/ARCHIVEPATH+RC+""

          ASSIGN/COPYEXCELSOURCE=ARCHIVEPATH+RC+""+PARTNO+"_"+SYST EMDATE("yyyy-MM-dd")+".xlsx"

          ASSIGN/COPYEXCELDEST=ARCHIVEPATH+RC+""+PARTNO+"_iss_"+IS SNO+"_RC_"+RC+"_PACK_"+SN1+".xlsx"

          V1 =FILE/EXISTS,COPYEXCELDEST
          IF/V1==1
          FILE/DELETE,COPYEXCELDEST
          END_IF/
          FILE/MOVE,COPYEXCELSOURCE,COPYEXCELDEST

          This is how I've got it running at the minute mate, just make sure that the new add-on is naming the file with just the system date and not the time

          btw archivepath is just aiming at the required directory.

          EDIT:

          I'm sure you already know this but you can use tracefields to automate the template:

          TRACEFIELD/DISPLAY=NO,REPORT=NO,DISPLAY MESSAGE=New Trace ; RC Number : RC
          TRACEFIELD/DISPLAY=NO,REPORT=NO,DISPLAY MESSAGE=New Trace ; Serial Number : SN1
          TRACEFIELD/DISPLAY=NO,REPORT=NO,DISPLAY MESSAGE=New Trace ; Part Number : PARTNO
          TRACEFIELD/DISPLAY=NO,REPORT=NO,DISPLAY MESSAGE=New Trace ; Date : SYSTEMDATE("dd MMM yyyy")
          TRACEFIELD/DISPLAY=NO,REPORT=NO,DISPLAY MESSAGE=New Trace ; Time : SYSTEMTIME("HH:mm")
          TRACEFIELD/DISPLAY=NO,REPORT=NO,DISPLAY MESSAGE=New Trace ; Operator : OPNO
          TRACEFIELD/DISPLAY=NO,REPORT=NO,DISPLAY MESSAGE=New Trace ; Machine ID : MCNO
          TRACEFIELD/DISPLAY=NO,REPORT=NO,DISPLAY MESSAGE=New Trace ; Quantity : QUAN

          EDIT 2: Ignore everything I just posted, I realise now you wanted to append the excel file and not just create a new one for each part!

          I do that through a custom .exe I've made so I'll definitely be interested to see it done with the new add-on!
          Last edited by JackMido; 09-02-2019, 06:59 AM.
          Clearance is clearance.

          Comment


          • #6
            There is an example template included in the installation, "Five part study" which shows how to do it. The Help is also quite helpful…

            You include a call to the report in every run, the report fills up sideways, just like your first picture. Just remember that you have to create the template for your maximum number of runs, it is not automatically expanded for each run.
            AndersI
            SW support - Hexagon Metrology Nordic AB

            Comment


            • michaelwildschutz
              michaelwildschutz commented
              Editing a comment
              Anders.
              I had already thought of that and I have been trying to get the 5 part study template to work but I cant even get that one to fill out all 5 positions..I even tried looping the program but it only keeps filling in part #1 even with the end loop before the excel command .I wanted to see how they got it to populate all 5 parts but I could not even get that to work properly..When I try to edit there 5 part study template to see how they configured each colum to capture the correct part but I don't see how they are doing it..

            • AndersI
              AndersI commented
              Editing a comment
              Are you sure you have removed the date and time from the file name?

          • #7
            I would like to actually see a complete example of appending the report data to a excel report. I have not been able to get the new Excel Form Report to output the appending data results as shown per example. I can do something similar to this example with DataPage. Another issue you cannot use the .xlsm extension type for a template like you can with DataPage.
            Example:

            Apeending Excel Reporting.PNG
            Last edited by davehocum; 09-03-2019, 08:29 AM.

            Comment


            • Kp61dude!
              Kp61dude! commented
              Editing a comment
              can't use .xlsm?!!!! Thanks for that... guess that's another 'feature' from Hex I won't be touching.. for now.

          • #8
            To get it to append data you need two things:

            1. A non-unique file name (no date and time included in the name)
            2. Duplicates of the {Meas} tag (PC-DMIS will only fill up to the given number of {Meas} tags, it doesn't automatically expand the template). See the Five Part Study example template.

            (Swedish, sorry - {Mätt värde} is the translation of {Meas} )

            FivePartStudy.PNG

            AndersI
            SW support - Hexagon Metrology Nordic AB

            Comment


            • davehocum
              davehocum commented
              Editing a comment
              What about the number row appending? From what I am finding is that you can only add one set of data area for row or column. Meaning you would also need to set a data area on the row for number line in your example (#1, # 2, # 3, # 4, # 5).
              For the example I gave in the previous post the Operator, Lot #, Serial # and Cavity # from the trace fields will need a separate data area set for each row. I have not found a way to do this with the Excel Form Report.

          • #9
            Hear is example of the what would have to be set for the data area highlighted in yellow.
            Set Data Area.PNG

            Comment


            • #10
              Originally posted by davehocum
              What about the number row appending? From what I am finding is that you can only add one set of data area for row or column. Meaning you would also need to set a data area on the row for number line in your example (#1, # 2, # 3, # 4, # 5).
              For the example I gave in the previous post the Operator, Lot #, Serial # and Cavity # from the trace fields will need a separate data area set for each row. I have not found a way to do this with the Excel Form Report.
              If you define the data area as a row, the rows are automatically replicating for each dimension. If you replicate the {Meas} on the row, PC-DMIS fills the same worksheet in the next free column (that has {Meas}) when you run the program again. No other places are filled again, AFAIK.

              And vice versa if you define the data area as a column.

              You can only have one replicating data area, Everything else has to be created with static references.

              Your picture is a bit small, but I don't think it can be created directly by the current Excel Form Report. Maybe by two Form Reports (one for each run-replicated area) and a Copy&Paste in Excel...
              AndersI
              SW support - Hexagon Metrology Nordic AB

              Comment


              • AndersI
                AndersI commented
                Editing a comment
                Well, it's verison 1.0 - they don't know what's needed until we tell them…
                But don't spend too much time on it yet - it looks like it's not working as expected if I add more than one Excel Form Report to the pp.

              • davehocum
                davehocum commented
                Editing a comment
                Thank you for the info. Question ? If they do not know what's needed then why are they the one's creating it ?

              • AndersI
                AndersI commented
                Editing a comment
                Well, the current design covers a lot of applications, but there will always be someone with the need of a more complicated design...

            • #11
              I understand. I did not realize what I was doing was complicated.

              Comment


              • #12
                so how can we append the same report and add every individual part run to populate the same report like I have shown here
                I have tried but all I can get is it just over writes the same line over and over


                excellCapture.JPG


                plus for some reason my code for date and time are not working in 2019R1



                systemdate and time Capture.JPG

                Michael A Wildschutz Sr
                I Walk on The WildSide
                "To Each is Own"

                Comment


                • #13
                  I gotta reiterate, WRITELINE output to CSV will do what you are trying to accomplish... it's just not fancy looking.

                  RE your date output:
                  According to F1 help, you need to define variables within assigments, then reference the assignments for tracefields to work.
                  also, you need to follow F1 syntax for systemdate and systemtime. this is the rare occasion that you need to use lowercase characters. Note: HH is 24hr, hh is 12hr

                  Code:
                  Assign/DATE= SYSTEMDATE("MM'.'dd'.'yyyy")
                  Assign/TIME= SYSTEMTIME("HH:mm:ss")
                  TRACEFIELD/DISPLAY=NO,REPORT=YES,DISPLAY MESSAGE=DATE/TIME"DATE/TIME; DATE AND TIME : DATE + " " + TIME
                  that should work.

                  Comment


                  • michaelwildschutz
                    michaelwildschutz commented
                    Editing a comment
                    thanks my problem mostly was the caps on mm and yyyy and dd

                • #14
                  Originally posted by michaelwildschutz View Post
                  so how can we append the same report and add every individual part run to populate the same report like I have shown here
                  Same problem as for davehocum - you want two separate replicated areas (column-wise for the 'header', row-wise for the runs). As far as I can discern, the current Excel Form Report only supports one - either row-wise or column-wise. This row (or column) can be augmented to add some fields from subsequent runs at the end of the row (bottom of the column) as the "Five Part Study" example shows.
                  AndersI
                  SW support - Hexagon Metrology Nordic AB

                  Comment


                  • #15
                    If you look back at my report what I have done was to pre set the information in the green shaded area to all ready be there in my template so I do not need that all I need is to have the measured result which I have finally gotten to work like below newexcellCapture.JPG


                    The problem is now that I need to have it populate the same report for every part run ..so right below I need to have the next part print the results appended all on one master sheet.. What the excel currently does is to save a new report with only that part results on it each time. and I cant figure it out..So I have been playing with the old way of Read and Write commands but it doesn't seem to work correctly I probably have something wrong.. readandwriteCapture.JPG
                    Michael A Wildschutz Sr
                    I Walk on The WildSide
                    "To Each is Own"

                    Comment

                    Related Topics

                    Collapse

                    Working...
                    X