Excel

Run a macro or function from another workbook

Ease of Use

Intermediate

Version tested with

97, 2003 

Submitted by:

Ken Puls

Description:

This code can be used to run a macro from another workbook. It must open the file first, and then the other workbook's macro can be executed. 

Discussion:

When running macros from other workbooks, I highly recommend reviewing your macros to ensure that they will operate on the sheets that they are supposed to. If using Activesheet, Activecell, etc... in your code, make sure that you do explicitly set the correct sheet (cell) as active, or you could encounter unexpected results. Following are two macros; one to run a sub with no arguments, and one to run a sub which does require arguments. 

Code:

instructions for use

			

Option Explicit 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 'Set file name and location. You will need to update this info! NameOfFile = "MyMacroLivesHere.xls" PathToFile = "C:\temp" '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 'Run the macro! (You will need to update "MacroName" to the 'name of the macro you wish to run) Application.Run (wbTarget.Name & "!MacroName") 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 Sub RunMacro_WithArgs() 'Macro purpose: To use the application.run method to execute 'a function or macro (with arguments) from another workbook Dim PathToFile As String, _ NameOfFile As String, _ wbTarget As Workbook, _ MyResult As Variant, _ CloseIt As Boolean 'Set file name and location. You will need to update this info! NameOfFile = "MyFunctionLivesHere.xls" PathToFile = "C:\temp" '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 'Run the function. Update the "FunctionName" to the name of your function 'and change 1 & 2 to the arguments you need to pass to the function MyResult = Application.Run(wbTarget.Name & "!Functionname", 1, 2) 'Give user the results MsgBox MyResult 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

How to use:

  1. Go to the workbook that holds the routine you want to run and note the exact name of the macro/function, and any arguements required.
  2. Copy the appropriate sub above.
  3. In the Workbook that you want to trigger your macro from (not the one that holds the code you want to execute)...
  4. In Excel press Alt + F11 to enter the VBE.
  5. Press Ctrl + R to show the Project Explorer.
  6. Right-click desired file on left (in bold).
  7. Choose Insert -> Module.
  8. Paste code into the right pane.
  9. Modify the NameOfFile and PathToFile variables to the appropriate workbook name and path of the file that holds the code you want to run.
  10. Update the Application.run line to list your target macro's name, and arguments (If necessary.)
  11. Press Alt + Q to close the VBE.
  12. Save workbook before any other changes.
 

Test the code:

  1. Press Alt + F8.
  2. Choose the macro that you copied in above.
  3. INSTRUCTIONS FOR ATTACHED EXAMPLE FILE:
  4. Download the file and unzip it (contains two workbooks).
  5. Note the directory it was unzipped to.
  6. Open AppRunExample.xls.
  7. Update the file path in cell B8 from c:\temp to wherever you unzipped the file.
  8. Click the buttons to see the results.
 

Sample File:

AppRunExampleFiles.zip 16.94KB 

Approved by mdmackillop


This entry has been viewed 414 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express