Quote in filename causes problem with Application.Run

[Carolyn]

I am having problems trying to call a function when the
workbook name contains a quotation mark. The code which
works when there is no quote in the name is:

WbVer = Application.Run("'" &
Application.ActiveWorkbook.Name & "'!VerifySS", SSVerKey)

VerifySS is a public function within a module in the
activeworkbook file. It is being called by code in an xla
which is activated by a toolbar button.

This works when there is no quote in the filename but the
string gets understandable confused by a quote in the
name. (eg "O'Neil File.xls") I tried to surround it with
double quotes, ['s etc but cannot get it working as any
time I build the run command adding the !functionname, it
becomes a string and the quote gets interpreted.

I would really appreciate any advice on this. As a last
resort, I can block file names being created with a quote
but the users tell me they need to do this.

Thanks and regards
Carolyn
Any help in

 

[Tom Ogilvy]

Try this:

sStr = ActiveWorkbook.Name
sStr = Application.Substitute(sStr,"'","''")
WbVer = Application.Run("'" & _
sStr & "'!VerifySS", SSVerKey)


replace each single quote with two single quotes within the string itself.

 

[Carolyn]

Thank you so much. It works a treat!

-----Original Message-----
Try this:

sStr = ActiveWorkbook.Name
sStr = Application.Substitute(sStr,"'","''")
WbVer = Application.Run("'" & _
sStr & "'!VerifySS", SSVerKey)


replace each single quote with two single quotes within the string itself.

--
Regards,
Tom Ogilvy

I am having problems trying to call a function when the
workbook name contains a quotation mark. The code which
works when there is no quote in the name is:

WbVer = Application.Run("'" &
Application.ActiveWorkbook.Name & "'!VerifySS", SSVerKey)

VerifySS is a public function within a module in the
activeworkbook file. It is being called by code in an xla
which is activated by a toolbar button.

This works when there is no quote in the filename but the
string gets understandable confused by a quote in the
name. (eg "O'Neil File.xls") I tried to surround it with
double quotes, ['s etc but cannot get it working as any
time I build the run command adding the !functionname, it
becomes a string and the quote gets interpreted.

I would really appreciate any advice on this. As a last
resort, I can block file names being created with a quote
but the users tell me they need to do this.

Thanks and regards
Carolyn
Any help in

.