How to run macros in another workbook
2009-07-21 VBA programming 0 95
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 SubIf 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))