Using a single quote in VBA. The Single quote is a part of the pathname of a Sharepoint folder.

[MarcBergmans]

Hello,

I'm asked to change an existing VBA Macro.
The macro store pathnames in variable and then in a loop the cells of the excel sheets in the variable are filled.
In the pathname there is a ' (single quote). Example \2021 Q3 A&S\10. QRT's.
It a file on a Sharepoint-site.
When i removed the ' in the string and make a directory with the name 10. QRTs, the macro works.
A possibility is changing the name of the folder, but i'm not authorized to do this, so a method to use ' in a string would be a good solution.
I searched on the internet but the proposals I found doesn't work.
I tried to put the ' between Double quotes, to put the quote between parenthesis(), to concatenate the ' with other characters in a new other variable and using this variable in the folder name.
But until now I didn't find a method that works.
I work with O365.
Do someone knows a solution

Thank you in advance

My best regards,

 

[RoryA]

Welcome to the forum.

How is your code actually using the paths? If it's in a formula, you need to double the quote in your code - i.e. store it using something like Replace$(thePath, "'", "''")