Control Parameter Removing Whitespace & Causing Errors

[DawnD]

Hello,

I'm having a problem with a control parameter removing whitespace from a filepath, which then causes an error in opening the file. The file in question has a leading space (I know that's bad practice but it's out of my control and I can't change it). The filepath is brought in via the directory tool and then a formula adds the sheet name, then it's fed into a macro to iterate through the entire folder of excel files. However, with this particular file, the filepath is: C:\Example\Sub-folder\ Workbook.xlsx|||`Sheet1$` and I'm getting the error:

Can't find the file C:\Example\Sub-folder\Workbook.xlsx|||`Sheet1$`

Is there anything I can do to stop it from trimming that leading whitespace?

Many thanks!

[FinnCharlton]

Hi @DawnD , 

In a quick test I can't seem to replicate this, my control parameter replicates whitespace:

Is the filename after the directory tool correct?

Also, try changing the control parameter constant in your macro file to "C:\Example\Sub-folder\ Workbook.xlsx|||`Sheet1$`

Run the macro, does the whitespace remain?

[DawnD]

Thanks for the quick reply :) 

The filename is definitely correct going into the macro. It comes out of the directory with the space, goes through several tools, none of which remove the space, then it goes in but the macro can't open the file. 

I tried changing the macro as you suggested so that the Input Data tool linked to the control parameter opens the file in question and I get an error - Can't find the file: ... Even when I navigate to the file and select it from within the tool. So I guess the problem is with the Input Data tool rather than the Control Parameter?

[FinnCharlton · Accepted solution]

Hi @DawnD ,

Seems like the input data tool automatically trims whitespace before filenames. I'm not sure this will work, but try opening the file using ' * ' instead of the space, for example: C:\Example\Sub-folder\*Workbook.xlsx|||`Sheet1$`

[DawnD]

Thanks!!