Iinserting a Excel Report at end or Cmm program

[michaelwildschutz]

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

[michaelwildschutz]

Jeffman or Anders any Ideas on this new excel report feature

[louisd]

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...

[michaelwildschutz]

Don Rugerri any ideas on this new excel report

[JackMido]

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!

[AndersI]

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.

[michaelwildschutz]

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]

Are you sure you have removed the date and time from the file name?

[davehocum]

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

[Kp61dude!]

can't use .xlsm?!!!! Thanks for that... guess that's another 'feature' from Hex I won't be touching.. for now.

[AndersI]

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

[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.

[davehocum]

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

[AndersI]

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]

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]

Thank you for the info. Question ? If they do not know what's needed then why are they the one's creating it ?

[AndersI]

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

[davehocum]

I understand. I did not realize what I was doing was complicated.

[michaelwildschutz]

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

[louisd]

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

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.

[michaelwildschutz]

thanks my problem mostly was the caps on mm and yyyy and dd

[AndersI]

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.

[michaelwildschutz]

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