Consulting

Results 1 to 5 of 5

Thread: Bug in Run.Application Excel 2003 - dash in filename

  1. #1

    Bug in Run.Application Excel 2003 - dash in filename

    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

  2. #2
    Have you tried the project.module.sub syntax?
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

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

    thanks for responding
    Cam

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,445
    Location
    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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •