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