can't use Application.Run() with apostrophe in file name

I need to use the application.run method to invoke macros in workbooks I
create. Unfortunately, the workbooks can contain apostrophes in the file
name.

I tried double quoting the filename if it contains an apostrophe, but
that didn't work:

*double quote the file name if it contains an apostrophe
lcCAMNotebookFileName = loCAMNotebook.Name
IF CHR(39) $ lcCAMNotebookFileName
lcMacroName = [''] + lcCAMNotebookFileName + [''] + "!" +
EXCEL_MACRO_NAME
ELSE
lcMacroName = ['] + lcCAMNotebookFileName + ['] + "!" +
EXCEL_MACRO_NAME
ENDIF

*--run the Excel formatting macros
TRY
loExcelApp.Run( lcMacroName )
CATCH TO loErr
brserror( "couldn't find macro " + EXCEL_MACRO_NAME)
SET STEP ON
DEBUGOUT loerr.ErrorNo
DEBUGOUT loerr.Message
ENDTRY

no brackets
single quotes outside
repeat existing singlequote

Application.Run "'no''show.xls'!myProc"





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Woody wrote :

> I need to use the application.run method to invoke macros in
> workbooks I create. Unfortunately, the workbooks can contain
> apostrophes in the file name.
>
> I tried double quoting the filename if it contains an apostrophe, but
> that didn't work:
>
> *double quote the file name if it contains an apostrophe
> lcCAMNotebookFileName = loCAMNotebook.Name
> IF CHR(39) $ lcCAMNotebookFileName
> lcMacroName = [''] + lcCAMNotebookFileName + [''] + "!" +
> EXCEL_MACRO_NAME
> ELSE
> lcMacroName = ['] + lcCAMNotebookFileName + ['] + "!" +
> EXCEL_MACRO_NAME
> ENDIF
>
> *--run the Excel formatting macros
> TRY
> loExcelApp.Run( lcMacroName )
> CATCH TO loErr
> brserror( "couldn't find macro " + EXCEL_MACRO_NAME)
> SET STEP ON
> DEBUGOUT loerr.ErrorNo
> DEBUGOUT loerr.Message
> ENDTRY

Doubling-up the apostrophe works for me. Don't forget leading and trailing
apostrophes as well

Application.Run "'VPinMame Roms''99.xls'!Bob()"

--
HTH

Bob Phillips

"Woody" <[email protected]> wrote in message
news:[email protected]...
> I need to use the application.run method to invoke macros in workbooks I
> create. Unfortunately, the workbooks can contain apostrophes in the file
> name.
>
> I tried double quoting the filename if it contains an apostrophe, but
> that didn't work:
>
> *double quote the file name if it contains an apostrophe
> lcCAMNotebookFileName = loCAMNotebook.Name
> IF CHR(39) $ lcCAMNotebookFileName
> lcMacroName = [''] + lcCAMNotebookFileName + [''] + "!" +
> EXCEL_MACRO_NAME
> ELSE
> lcMacroName = ['] + lcCAMNotebookFileName + ['] + "!" +
> EXCEL_MACRO_NAME
> ENDIF
>
> *--run the Excel formatting macros
> TRY
> loExcelApp.Run( lcMacroName )
> CATCH TO loErr
> brserror( "couldn't find macro " + EXCEL_MACRO_NAME)
> SET STEP ON
> DEBUGOUT loerr.ErrorNo
> DEBUGOUT loerr.Message
> ENDTRY