What to do when your String variable contains an apostrophe

[jardenp]

I am trying to assign a workbook name to a String variable

Dim WeeklyRoutesWorkbook As String
    WeeklyRoutesWorkbook = ActiveWorkbook.Name

When I try to call this later, I get an error

 Range("G2").FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[" & WeeklyRoutesWorkbook & "]Sheet1'!C5:C9,5,FALSE)"

My testing shows this is because the WeeklyRoutesWorkbook contains an apostrophe (i.e., the original ActiveWorkbook name contains an apostrophe).

Is there a way around this? This script will be run on many workbooks, which all contain an apostrophe in their names.

I looked at this but couldn't make sense of it: https://msdn.microsoft.com/en-us/library/office/aa212167(v=office.11).aspx

Thanks!

JP in IN

 

[slinky]

Caveat - This isn't helpful at all..

Why are your users creating files with an apostrophe in the file name?!

 

[RoryA]

You need to double the apostrophe as you would entering the formula manually:

 Range("G2").FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[" & Replace$(WeeklyRoutesWorkbook, "'", "''") & "]Sheet1'!C5:C9,5,FALSE)"

 

[jardenp]

Because they're clearly not as tech savvy as you, Slinky.

I've stopped the practice, but that doesn't change the old files.

 

[jardenp]

Thank you, RoryA. It's much appreciated!

 

[jardenp]

I just tested this and it works. Thanks again, RoryA