View Full Version : Bug in Run.Application Excel 2003 - dash in filename

12-19-2007, 12:00 PM
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
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
'If the target workbook was already open, reactivate this workbook
End If

End Sub

12-20-2007, 04:25 PM
Have you tried the project.module.sub syntax?

12-21-2007, 07:01 AM
I am unfamiliar with project.module.sub. Still a beginner. Can you show me how to use this?

thanks for responding

Bob Phillips
12-21-2007, 03:58 PM
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

Application.Run "'" & wbTarget.Name & "'!ThisWorkbook.Opening"

12-21-2007, 04:48 PM
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.