PDA

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



ctmurray1
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
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
12-20-2007, 04:25 PM
Have you tried the project.module.sub syntax?

ctmurray1
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
Cam

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"

ctmurray1
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.