Quote in filename causes problem with Application.Run
 

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

Quote in filename causes problem with Application.Run
 

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

Quote in filename causes problem with Application.Run
 

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




.