Run Time 1004 error when calling sub from another macro

[Curben]

I am tryingto run a macro existing in another workbook opened by a variable, I am using the following code to try and get it to work but i get Run-time error '1004':

Cannot run the macro "Test 5.xlsm'!UpdTables'. The macro may not be available or all macros may be disabled.

Application.Run "'" & Application.ActiveWorkbook.Name & "'!UpdTables"

 

[ravi.lele]

That helped me find the ID-10-T error
Trying things on my own I switched from

Application.Run "'" & Application.ActiveWorkbook.Name & "'!UpdTables"

TO

Application.Run Application.ActiveWorkbook.Name & "!UpdTables"

the test you gave showed me the slight syntax difference and I reverted the call, it works now thanks everyone.

--------------------------------------

Above code works if the Macro does not require a parameter and fails if parameters are required.
Can anyone please let me know how to call a macro placed in another workbook with parameters?

 

[Andrew Poulsom]

Welcome to MrExcel.

This may help you:

http://support.microsoft.com/kb/153307

 

[ravi.lele]

Welcome to MrExcel.

This may help you:

How to call Microsoft Excel macros that take parameters

Thanks for the prompt reply, but error is still same.

 

[Andrew Poulsom]

What error do you get with what code?

 

[ravi.lele]

What error do you get with what code?

ERROR IS

Run-Time Error: '1004': Cannot run macro The macro may not be available in this workbook or all macros may be disabled

I am on Excel 2010.

 

[RoryA]

with what code?

 

[ravi.lele]

with what code?

Sub CallingMacro()

'Try #1 --------NOT WORKING
'Application.Run "D:\WBContainingSASMacro.xlsm!SASMacro", "1", "2"

'Try #2 --------NOT WORKING
Dim wkbk As Workbook
Set wkbk = Workbooks.Open(Filename:="D:\WBContainingSASMacro.xlsm", ReadOnly:=True)
''Application.Run wkbk.Name & "!SASMacro" & "1" & "2"

'SUCCESS - sub without parameters
'1.1
'Application.Run "'" & wkbk.Name & "'!DontCall"
'1.2
'Application.Run wkbk.Name & "!DontCall"

'FAIL - sub with parameters
'Application.Run "'" & wkbk.Name & "'!SASMacro" & " 1" & " 2"
'Application.Run wkbk.Name & "!SASMacro" & " 1" & " 2"

'wkbk.Close

'Try #3 --------NOT WORKING
Dim wkbk1 As Workbook
Set wkbk1 = Workbooks.Open(Filename:="D:\WBContainingSASMacro.xlsm", ReadOnly:=True)
wkbk1.Activate

Dim oExcelApp As Object
' Create a reference to the currently running excel application
Set oExcelApp = GetObject(, "Excel.application")
' Make the Excel Application Visible.
oExcelApp.Visible = True
' Run the excel procedure
oExcelApp.Run "SASMacro", "1", "2"

End Sub

-----------------------------------------
CALLED MACRO placed in Other File (WBContainingSASMacro.xlsm)
-----------------------------------------
Sub SASMacro(Param1 As String, Param2 As String)

MsgBox "SAS Macro Called" & vbNewLine & "Value of Param1 = " & Param1 & vbNewLine & "Value of Param2 = " & Param2

End Sub

 

[Andrew Poulsom]

Does this work as expected?

Application.Run "'" & wkbk.Name & "'!DontCall", "1", "2"

Why did you replace the commas with ampersands?

 

[ravi.lele]

Does this work as expected?

Application.Run "'" & wkbk.Name & "'!DontCall", "1", "2"

Why did you replace the commas with ampersands?

Sub CallingMacro()

'Sample #1
' SUCCESS
' Application.Run "'D:\WBContainingSASMacro.xlsm'!DontCall"
' Application.Run "'D:\WBContainingSASMacro.xlsm'!SASMacro", "1", "2"

'Sample #2
' SUCCESS
Dim wkbk As Workbook
Set wkbk = Workbooks.Open(Filename:="D:\WBContainingSASMacro.xlsm", ReadOnly:=True)
Application.Run "'" & wkbk.Name & "'!SASMacro", "1", "2"
wkbk.Close
End Sub


Thanks all of you, but i have got the solution, which is shown above.
Just tried with COMMAS around WB names and it worked.

Thanks again for all your suggestions and time.

 

[mp4-4]

That helped me find the ID-10-T error
Trying things on my own I switched from

Application.Run "'" & Application.ActiveWorkbook.Name & "'!UpdTables"

TO

Application.Run Application.ActiveWorkbook.Name & "!UpdTables"

the test you gave showed me the slight syntax difference and I reverted the call, it works now thanks everyone.

Sorry for reviving this dead thread but I'm having similar issues as Curben did.

Summary: I want Macro in Workbook A to call a Macro in Workbook B1. And while my procedure works on Worksheet B1, it doesn't work on Worksheet B2.
Workbook B2 is literally the same identical file, save some changes for 64-bit compliance (ptrsafe functions).

a) All macros are enabled
b) Macros reside in standard module in the workbook.
c) Workbook B2 does contain the macro in question

To talk with Worksheet B1, I use:

Application.Run Application.ActiveWorkbook.Name & "!Module1.UpdTables"</pre>where it works as intended. But doesn't work for B2.

I have tried using:

Application.Run Application.ActiveWorkbook.Name & "!UpdTables"
Application.Run "'" & Application.ActiveWorkbook.Name & "'!UpdTables"
Application.Run "'" & Application.ActiveWorkbook.Name & "'Module1.!UpdTables"</pre>
and none of these work. Can anyone advise?

Again, this is one of my few posts on MrExcel so I hope I'm not breaking any conventions/rules of the forum.