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
Code:
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
