Application.Run - Spaces and single quotes

Ok so I know I can run a function in another (open) workbook by doing the following line:

Please Login or Register  to view this content.

I'm also aware that if the workbook name contains spaces it must be wrapped in single quotes, thus:

Please Login or Register  to view this content.

My question is, how do I cope with it if the workbook name has spaces AND single quotes in the name? The single quote in the name effectively 'ends' the single quotes that are supposed to wrap the name, and I cannot see how I can escape the single quote character so that VBA realises it is part of the name, and not a delimiter.

Please Login or Register  to view this content.

Any ideas please? (Changing the target workbook name is not really an option as the workbooks are auto-named from elsewhere based on some input data).

Thanks!

Good morning Phil_V

Originally Posted by Phil_V

My question is, how do I cope with it if the workbook name has spaces AND single quotes in the name?

Untested, but try forcing the ' into the variable by calling it using its Char number (which is 39), thus :

Please Login or Register  to view this content.

HTH

DominicB

Thanks, but alas no, the wrkbookname string just ends up formed exactly as if I had typed the character in

You need to double up the apostrophe in the file name in the Run statement. For example:

Please Login or Register  to view this content.

Excellent, works like a dream

Thanks for that!

Glad to help.
Incidentally, a good way to figure out the syntax is to create a simple test macro in the workbook (with no parameters), switch to any other workbook, then bring up the macro dialog - it should list the macro in the form that application.run needs it.

Nice tip that, thanks