Bug in Run.Application Excel 2003 - dash in filename

[ctmurray1]

I got this code from this forum, it opens another workbook and runs a macro from that workbook. It fails if the file name contains a dash (Book-1.xls) but runs fine if the file name is un-dashed (Book1.xls). The error message says it cannot find the macro that I am calling, and shows the correct workbook and macro name in the error message. It does work if the path to the workbook contains dashes (thus the folders have dashes in their name), as long as the file name contains no dashes.

Interesting feature

Sub RunMacro_NoArgs()
     'Macro purpose:  Use the application.run method to execute
     'a macro without arguments from another workbook
     
    Dim PathToFile As String, _
    NameOfFile As String, _
    wbTarget As Workbook, _
    CloseIt As Boolean
    Dim namelength As Integer
     
     'Set file name and location. You will need to update this info!
     NameOfFile = "Book1.xls"
     PathToFile = "C:\Documents and Settings\us265779\Desktop"
     
     'Attempt to set the target workbook to a variable.  If an error is
     'generated, then the workbook is not open, so open it
    On Error Resume Next
    Set wbTarget = Workbooks(NameOfFile)
     
    If Err.Number <> 0 Then
         'Open the workbook
        Err.Clear
        Set wbTarget = Workbooks.Open(PathToFile & "\" & NameOfFile)
        CloseIt = True
    End If
     
     'Check and make sure workbook was opened
    If Err.Number = 1004 Then
        MsgBox "Sorry, but the file you specified does not exist!" _
        & vbNewLine & PathToFile & "\" & NameOfFile
        Exit Sub
    End If
    On Error GoTo 0

    Application.Run (wbTarget.Name & "!ThisWorkbook.Opening")
    
    If CloseIt = True Then
         'If the target workbook was opened by the macro, close it
        wbTarget.Close savechanges:=False
    Else
         'If the target workbook was already open, reactivate this workbook
        ThisWorkbook.Activate
    End If
     
End Sub

[Oorang]

Have you tried the project.module.sub syntax?

[ctmurray1]

I am unfamiliar with project.module.sub. Still a beginner. Can you show me how to use this?

thanks for responding
Cam

[Bob Phillips]

I have come across this before, and I solved it by enclosing the workbook name in single quotes, even though there are no embedded spaces

[vba]

Application.Run "'" & wbTarget.Name & "'!ThisWorkbook.Opening"
[/vba]

[ctmurray1]

This addition of single quotes did indeed solve the problem. Thanks.

I ended up changing all my filenames to not have dashes, but now I can use them again.