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

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



"Carolyn" wrote in message
...
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

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



"Carolyn" wrote in
message
...
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




.