How to run macros in another workbook
2009-07-21 VBA programming 0 275
The example macro below shows how you can run macros (procedures and functions) that are stored in another open workbook.
Sub ExamplesRunMacroInAnotherWorkbook()
Dim wb As Workbook, strProcedureName As String, strFunctionName As String
Dim varResult As Variant
Set wb = Workbooks("MyMacroWorkbookName.xls")
strProcedureName = "MyMacroProcedureName" '
strFunctionName = "MyMacroFunctionName"
' examples
Application.Run "WorkbookName.xls!ProcedureName"
Application.Run "'" & wb.Name & "'!" & strProcedureName
Application.Run "WorkbookName.xls!ProcedureName", 10, "SomeText" ' two arguments
Application.Run "'" & wb.Name & "'!" & strProcedureName, 10, "SomeText" ' two arguments
varResult = Application.Run("WorkbookName.xls!FunctionName", 10, "SomeText") ' two arguments
varResult = Application.Run("'" & wb.Name & "'!" & strFunctionName, 10, "SomeText") ' two arguments
Set wb = Nothing
End Sub
If you are going to run macros in an Excel workbook from a VBScript and you have to include any input arguments to the macro, you will probably get some runtime error messages complaining about a type mismatch. This is because all the variables in the VBScript are Variants, and your Excel macros probably needs input variables of another type, like String, Long, Boolean.
You can solve this problem by converting the variables you are passing from the VBScript to the Excel macro to the proper variable type like this:
' the variable objXL must contain an Excel Application object
' the variable objWB must contain an Excel Workbook object
objXL.Run "'" & objWB.Name & "'!ProcedureName", CStr(strStringVariable), CLng(lngLongValue), CBool(blnBooleanVariable)
blnResult = objXL.Run("'" & objWB.Name & "'!FunctionName", CStr(strStringVariable), CLng(lngLongValue), CBool(blnBooleanVariable))