Adding to default excel file name

[[Li...]]

How do I go about adding additional information to the default excel file naming. We rely heavy on identifying serial numbers when running parts. As it is we just have the program name and incremental part number. The engineers and QE's would like to serial numbers for the excel files.
I have attached a screenshot of a folder with excel data for one of our parts.

Lincoln

Excel report file name.xlsx

[[Ma...]]

Could you type in the serial number before the incremental part number in the input parameters?

[[Ow...]]

I'm not sure about changing the file name but, because the file name generally is the part number with the incremental part number added at the end, you could just put the serial number in the incremental number and add a -1, at the the end? Then the next part run would automatically update to the serial number -2.

File should be named -program name or part # number -serial number-1.
If it doesn't already, you can go to resources, report header parameters and at the bottom left of the input parameter window select the check box to "force input at start" and then you can change the incremental part number there before starting the program.


You could also edit the excel reports excel user fields and ini file to include the serial number the excel report header but, that doesn't change the file name.

[[Ow...]]

Yeah just like Marc Distefano said.
Maybe my wife is right, maybe I do make things sound more complicated 🙄

[[SH...]]

Please sign in to view this quote.

I have attached the video link for editing Excel report, look like an old video.

https://youtu.be/oJvFeYWJLbo

[[Th...]]

I've found a solution for myself, maybe you want to adapt it for your exact problem:

//Benennung erzeugen
ANa = "D:\CALYPSO\Funktionen\umbenennen\Ausgabe_Name_alt.txt"
ANn = "D:\CALYPSO\Funktionen\umbenennen\Ausgabe_Name_neu.txt"
Ausgabepfad = directoryPath("results") + "\"
Ausgabename = getRecordHead("planid") + "_" + getRecordHead("partnbinc") + ".xls"
Ausgabe_alt = Ausgabepfad + Ausgabename
if fileExists(ANa) then
	deleteFile(ANa)
endif
addToFile(ANa,Ausgabe_alt)
Ausgabename = FG + "_" + WW + "_" + getRecordHead("partnbinc") + ".xlsb"
Ausgabe_neu = Ausgabepfad + Ausgabename
if fileExists(ANn) then
	deleteFile(ANn)
endif
addToFile(ANn,Ausgabe_neu)

You just have to change the paths and put the code in the end options of your measurement plan. The files have to be put in the folder of the plan.

The code also changes the name of the .zmp file, created by the program to save the measured points.

Hope this helps.

EDIT: formatting as code

report_end.bat.txtreport_end_pcm.txt

[[Ja...]]

Please sign in to view this quote.

Anyone know why this wont work on a subsequent evaluation? At first I thought it was because of the "_SE" that it adds to the file name but after I removed that and unticked the box it still cant see/find the files to rename them.

[[Ro...]]

Are you sure subsequent isn't just overwriting the same file? open it and check the time.

FYI, i dont have PCm anymore so i cant really help troubleshoot like i used to.

[[Ri...]]

Please sign in to view this quote.

I know the file name can be changed, the system I am working on now names the excel file with specific verbiage.
I'm still trying to figure out where the name is changed and the new workbook is created so I can add some simple "ThisWorkbook SaveAs" code to eliminate manually saving the file.

[[Je...]]

You can easily adapt this... I used it for renaming an XLSX to XLS

Place it in "report_end_pcm.txt"

CalypsoRslt   = directoryPath("results")+"\"
EXCEL_BaseName = CalypsoRslt+getRecordHead("planid")+"_"+getRecordHead("partnbinc")
EXCEL_XLSX = EXCEL_BaseName+".xlsx"
EXCEL_XLS = EXCEL_BaseName+".xls"


if fileExists(EXCEL_XLSX)
	copyFile(EXCEL_XLSX,EXCEL_XLS)
	wait(1)
	deleteFile(EXCEL_XLSX)
endif

[[Ri...]]

I cant seem to get the file copy to work.
(Note: ServerPath is predefined as "I:" and is used in other parts of the PCM file.)
It runs thru with no errors, yet I don't get a copy of the file in the defined folder location.

CalypsoExcelRslt   = ServerPath
EXCEL_BaseName = CalypsoExcelRslt+"\"+getRecordHead("planid")+"_"+getRecordHead("partnbinc")+"_"+getRecordHead("time")
//EXCEL_XLSX = EXCEL_BaseName+".xlsx"
EXCEL_XLS = EXCEL_BaseName+".xls"


if fileExists(EXCEL_XLS)
	copyFile(EXCEL_XLS,"C:\Public\Documents\Zeiss\CALYPSO\workarea\results\results\Excel Files")+getRecordHead("planid")+"\"+EXCEL_BaseName)
	wait(1)
	//deleteFile(EXCEL_XLSX)
endif

[[Je...]]

post a message after the file check.
Personally I would define the path and file names as a single variable prior to the copy command and use only variables in the copy line.

if fileExists(EXCEL_XLS)
display("File Exists - copy routine commencing")
copyFile(...//your file paths and variables....)
wait(1)
else
message("No file found - verify filename and path string")
endif

[[Ja...]]

Please sign in to view this quote.

Does it work on a subsequent evaluation?

Edit: So it appears the one a subsequent evaluation Calypso does not write to "partnbinc", it must write that to a different variable somewhere.

Edit 2: So it appears I might need to use getRecordHeadM to access the header variables on a SE

[[Je...]]

getRecordHead for current data.
getRecordHeadM for stored header data from the stored run/MeasPoints

[[Ja...]]

Please sign in to view this quote.

Yeah I figured that out, but that's not quite right either. Because that seems to pull from inside the .zmp file. Which is ok, but if I rename the .zmp file, load the points, and run the SE it uses the name of the .zmp file as the part number in the results. Same goes if I put anything into the "extension part no." box. It adds that to the file name of the .zmp file and completely ignore what's inside the .zmp.

[[Ri...]]

Please sign in to view this quote.

This helped me see why no file is being copied.
I do not know how the Excel file name is being constructed via the Zeiss Characteristic.xlt template (and other templates and INI file. The macros are scattered about in sub and or functions through out.
I have too many excel ".xls" files in the predetermined drive to simply use a generic xls find event.

Thanks for the help, I'm going to go back to the VBA side and figure where to put the SaveAs code.
Once I find the last instruction in excel.

[[Je...]]

Personally, I'd stick with PCM. A Service Pack or CALYPSO upgrade may overwrite the characteristic.xlt without your knowledge in your CALYPSO directory.

Check out the US Portal Knowledge Base article on using PCM for Excel reporting
https://portal.zeiss.com/knowledge-base ... &id=722231

Note; Rather than adding the PCM script the Post-Settings, you can add to "report_end_pcm.txt" and place in the inspection plan directory so that it is valid for any program (or put in the specific inspection plan folder to be specific to that plan only)

[[Ja...]]

Please sign in to view this quote.

So I figured out what was going on with the subsequent evaluations. I have a version of the code above in an "report_end_pcm.txt" file. When doing an SE it wouldn't find the files to change them because it was looking for a different "partnbinc" value. I think what subsequent evaluation does when you hit load points is populate all the header data however you told it to. Then after it runs the program and BEFORE it executes the "report_end_pcm.txt" file it changes all the header data back. I'm pretty sure it changes it to whatever is in the "startfile" inside the program folder, but I didn't test that. So to get around that I built all the variables in the presettings.

[[Ün...]]

With the file "report_end_pcm.txt",

How can I change excel file location and title as below.

I have no knowledge about writing code, thanks for your help

*
*
"D:\OLCUM SONUCLARI\0576-015\DELIKDELME\"+getRecordHead("date")+"__"+getRecordHead("drawingno")+"__"+getRecordHead("u_field11")+"__"+getRecordHead("u_field8")+"__"+getRecordHead("u_field9")+"__"+getRecordHead("partnbinc")+".xls"

[[Je...]]

COPIED DIRECTLY FROM THE US KNOWLEDGE BASE WITHOUT IMAGES:

Sometimes it may be necessary to write a PiWeb report to the results folder while also generating an Excel report to a different folder location. The following example names the Excel report after the Measurement Plan name & incremental part number.

To start, add the following code to the PostSettings area of the program, (as shown in the capture):

filePathExcel = directoryPath("results") + "\" + getRecordHead("planid") + "_" + getRecordHead("partnbinc") + ".xls"
scriptPath = "Wscript " + directoryPath("results") + "\deleteFile.vbs"
newFolder="C:\Temp\"  + getRecordHead("planid") + "_"  + getRecordHead("partnbinc") + ".xls"


//wait(10)    //if the file is not copying, uncomment this delay to give it more time
if fileExists(filePathExcel) then
  copyFile(filePathExcel,newFolder)
  deleteFile(directoryPath("results") + "\temp.txt")
  addToFile(directoryPath("results") + "\temp.txt",  filePathExcel)
  systemCallWithWait(scriptPath)
endif

Edit the third line for "newFolder" (circled in orange in the Knowledge Base article) to include the path to the folder location. The example is using the C:\Temp\ folder. Replace this "C:\Temp\" with your custom folder location.

Take the code for the VBScript and copy it into a text file and save this in the results folder by doing Save As deleteFile.vbs (set the Save as type to AllFiles(*.*)

The VBScript code:

Dim filename1, strPath, strFolder
Dim objFSO, objFile
Dim objXls, objText, obj                                                'Declaring variables


strPath = Wscript.ScriptFullName


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(strPath)
strFolder = objFSO.GetParentFolderName(objFile)                         'Path to the folder this script is in


Set objXls = createobject("Scripting.FileSystemObject")                 'Creating a File Object to delete the Excel file
Set objText = createobject("Scripting.FileSystemObject")               'open-read-delete temp.txt


Set obj=objText.OpenTextFile(strFolder & "\temp.txt", 1)
filename1=obj.ReadLine
obj.Close


objText.DeleteFile strFolder & "\temp.txt"
Set objText=Nothing  


objXls.DeleteFile filename1                                             'DeleteFile Method is deleting the Excel file
Set objXls=Nothing    

Run the Excel report the normal way, allowing it to write the report to the results folder. The PCM will copy the report to the custom folder location and then call the script, which will delete the original Excel report.

[[Ri...]]

My work around that now generates Excel files without duplication or loss of traceability.
We use a PCM file called: calculation_end_pcm.txt
In that file I added the following code.

//added by Rick Stanich 06.28.23
//Sets "partnbinc" (incremental Counter) to "planid" and "partnbinc" and Time.nTime = time()
nTime=time()
nHour = strElement(1,":",nTime)
nMinute = strElement(2,":",nTime)
nSecond = strElement(3,":",nTime)
nTimeFile = nHour+nMinute+nSecond
setRecordHead("partnbinc",getRecordHead("u_Station")+"_"+getRecordHead("u_SerialNo")+"_"+getRecordHead("date")+"_"+nTimeFile)
partnbinc2=getRecordHead("partnbinc")
//end 06.28.23

This creates a string for "partnbinc" which is used by the "xlt" macros to generate an Excel file.
The variable "Time()" creates a date and time stamp, but it seems when passed to Excel it stays in native Date and Time nomenclature. This causes the Excel file to fail due to invalid characters.
Example: 2023-7-18 11:32:6 PM
The above code (Written by Zeiss forum member: Jens Kröcher) viewtopic.php?t=11497
Removes the colons from the time stamp to produce: 023-7-18 11326 PM.

Now we generate Excel file names without issues or interactions from Subsequential Evaluations.