Why does Excel add "%20" as spaces in file naming?

[Rick_Stanich]

I am creating an Excel file from CMM software.
I generate the name via a language called "PCM". Its similar to VBA.
I am running into an issue where there is a space between words/Letters/etc; Excel adds "%20" in place of the "space".

The "PCM" scripts are not creating a string with "%20", its only when the string is passed to Excel.

Example file naming:
Ruby Head_Rev 2_Op 70_M0070-Test4_DeleteMe_2023-7-12_85844 am.xls
Becomes:
Ruby%20Head_Rev%202_Op%2070_M0070-Test4_DeleteMe_2023-7-12_85844%20am.xls
When I save the file the "%20" stays in the file name, or I have to manually edit the file name.

This is not happening on all installs of Excel.

Any help is appreciated.

[Glenn Kennedy]

Perrhaps it's because special characters in URLs are usually expressed using the percent sign and a sequence of numbers. For spaces, this is %20.

[6StringJazzer]

I am creating an Excel file from CMM software.
I generate the name via a language called "PCM". Its similar to VBA.
I am running into an issue where there is a space between words/Letters/etc; Excel adds "%20" in place of the "space".

The "PCM" scripts are not creating a string with "%20", its only when the string is passed to Excel.

Since PCM is not a commonly known language for Excel we need a lot more information about how this all works. How does a PCM script run? How does it interact with Excel? How is the file name being used once received by Excel and where is it being changed? Where are you saving the file--local drive, OneDrive, SharePoint?

The %20 token is used in URLs because spaces are not allowed. I suspect that somewhere in your chain a component is treating this string as a URL and encoding it. But you have a somewhat unique tech stack here so we need more information.

BTW I would recommend not using the .xls format.

[Glenn Kennedy]

The %20 is actually a "code" for a space. The percent sign indicates the start of the code, and the two digits after the percent sign indicate the hexadecimal value of the character. 20 (hex) is the same as 32 (decimal), which is the ASCII value for a space.

So it's the hexadecimal code for a space...

[Rick_Stanich]

Since PCM is not a commonly known language for Excel we need a lot more information about how this all works. How does a PCM script run? How does it interact with Excel? How is the file name being used once received by Excel and where is it being changed? Where are you saving the file--local drive, OneDrive, SharePoint?

The %20 token is used in URLs because spaces are not allowed. I suspect that somewhere in your chain a component is treating this string as a URL and encoding it. But you have a somewhat unique tech stack here so we need more information.

BTW I would recommend not using the .xls format.

I will change the format to xlsx, thank you.

This is the bit of code from PCM that generates a string for the file name.

//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") //this is passing a Calypso variable to a user varaible
//end 06.28.23 

"nTime" is a variable, "time()" is retrieving the system time via Calypso software.
The four user variables break down the time from Clock to digits with "am" or "pm"
nHour
nMinute
nSecond
nTimeFile

Example:
8:58:44 becomes "85844 am"

This creates the file name string

setRecordHead("partnbinc",getRecordHead("u_Station")+"_"+getRecordHead("u_SerialNo")+"_"+getRecordHead("date")+"_"+nTimeFile) 

Where "partnbinc" is a Calypso variable and everything after is an accumulation of other variables.
Example:
The variable "partbinc" = "Ruby Head_Rev 2_Op 70_M0070-Test4_DeleteMe_2023-7-12_85844 am"

The variable "partbinc" is passed to Excel via an Excel XLT file (Written by the software company who makes Calypso).
And that's where it gets hard to follow.
The XLT file is identical on all CMM PC's.


The above works fine on one PC Excel install but not this PC Excel install.
This is at a large corporation, the excel versions are the same.

[6StringJazzer]

The variable "partbinc" is passed to Excel via an Excel XLT file (Written by the software company who makes Calypso).

That's the key step. An xlt file it just a template. So there could be anything in there, and that's probably where the issue is. Not clear to me how the variable is being passed from PMC to an Excel file, or at what step this is being interpreted as a file name. Is the PMC code opening the Excel file and using an API to insert the data? Is the Excel file referencing it from a common data environment? Does the Excel file have VBA that pulls it from somewhere?