Calling workbooks with apostrophes in name?

[Don Wiss]

In some of my spreadsheets I have one workbook run a macro in another open
workbook. The name of the workbooks are generally created by a macro, and
any apostrophes are removed. But sometimes the user puts them back in. And
it would be nicer to not remove them in the first place. An example line of
code that I'm using is:

Application.Run ("'" & TargetName & "'!HidePolicyPeriods")

How could it be written so apostrophes could be in TargetName?

Don <donwiss at panix.com>.

 

[Harlan Grove]

In some of my spreadsheets I have one workbook run a macro in another open
workbook. The name of the workbooks are generally created by a macro, and
any apostrophes are removed. But sometimes the user puts them back in. And
it would be nicer to not remove them in the first place. An example line of
code that I'm using is:

Application.Run ("'" & TargetName & "'!HidePolicyPeriods")

How could it be written so apostrophes could be in TargetName?

When in doubt, experiment. Create a dummy file with an embedded single quote
in the filename. Then open another file and write a formula that refers to a
cell in the first workbook. Once you've entered the formula, how does the
filename appear? On my system, the single quotes are doubled, so an external
reference to Sheet1!A1 in a file named foo'bar.xls appears as

='[foo''bar.xls]Sheet1'!A1

So try doubling the single quotes.

 

[Don Wiss]

Don Wiss wrote...

In some of my spreadsheets I have one workbook run a macro in another open
workbook. The name of the workbooks are generally created by a macro, and
any apostrophes are removed. But sometimes the user puts them back in. And
it would be nicer to not remove them in the first place. An example line of
code that I'm using is:

Application.Run ("'" & TargetName & "'!HidePolicyPeriods")

How could it be written so apostrophes could be in TargetName?

When in doubt, experiment. Create a dummy file with an embedded single quote
in the filename. Then open another file and write a formula that refers to a
cell in the first workbook. Once you've entered the formula, how does the
filename appear? On my system, the single quotes are doubled, so an external
reference to Sheet1!A1 in a file named foo'bar.xls appears as

='[foo''bar.xls]Sheet1'!A1

So try doubling the single quotes.

The TargetName is under program control. So what I would have to do is to
write a function that doubled up any single apostrophe, that wasn't already
doubled. What I was hoping to find was an alternative construct that didn't
use apostrophes in the code.

Don <donwiss at panix.com>.

 

[Harlan Grove]

The TargetName is under program control. So what I would have to do is to
write a function that doubled up any single apostrophe, that wasn't already
doubled. What I was hoping to find was an alternative construct that didn't
use apostrophes in the code.

As long as your filenames *could* include embedded single quotes, you're
going to *require* some means of doubling them when used as workbook names
to identify workbooks in Excel's object model. The only alternative involves
using persistent workbook-type object variables set when each file is
opened, but the precise approach to doing so depends on precisely how you're
opening these workbooks.

 

[Peter T]

An example line of code that I'm using is:

The TargetName is under program control. So what I would
have to do is to write a function that doubled up any
single apostrophe, that wasn't already doubled. What I
was hoping to find was an alternative construct that
didn't use apostrophes in the code.

Try:

TargetName = Application.Substitute(TargetName, "'", "''")
' ie single ' with two '
'or use Replace if sure only post xl97 version

TargetName = "'" & TargetName & "'" & "!"

Application.Run (TargetName & "tt")

The final embracing with apostrophes will not do any harm
if not required and also catch for other characters such
as spaces and dashes in the filename which also need
handling.

Regards,
Peter