One of the more common questions I get revolves around running an Excel macro from Access. That is, how do you fire an existing Excel macro from Access.
I'll share the code to do that in a moment.
I first want to point out that this code can also come in handy if you need to run an external Excel macro in another workbook. That is to say, while you are working in an Excel file, you can reach out and run a macro in another workbook.
OK. Here it is. Simply paste this code in a new module.
-
Sub RunExcelMacro()
-
Dim xl As Object
-
-
'Step 1: Start Excel, then open the target workbook.
-
Set xl = CreateObject("Excel.Application")
-
xl.Workbooks.Open ("C:\Book1.xlsm")
-
-
'Step 2: Make Excel visible
-
xl.Visible = True
-
-
'Step 3: Run the target macro
-
xl.Run "MyMacro"
-
-
'Step 4: Close and save the workbook, then close Excel
-
xl.ActiveWorkbook.Close (True)
-
xl.Quit
-
-
'Step 5: Memory Clean up.
-
Set xl = Nothing
-
-
End Sub
Some notes on the code:
Step 1: Create a new instance of Excel, open the target workbook; the workbook that contains the macro you need to run.
Step 2: Making Excel Visible. By default, Excel will open and run in the background (invisible to you). You can set the Visible property to TRUE if you need to see the workbook. If you don't want to see the Excel workbook while the macro is running, simply set the Visible property to FALSE.
Step 3: Run the target macro
Step 4: Close and save the target workbook. The True argument in xlwkbk.Close(True) indicates that you want the workbook saved after the macro has run. If you do not want to save the target workbook, change this argument to False. Also in Step 4, you quit the Excel application, effectively closing the instance of Excel.
Step 5: Release the objects assigned to your variables, reducing the chance of any problems caused by rogue objects that may remain open in memory.
RELATED STUFF
- Using Access to Combine Multiple Excel Files: Method 1
- VBE Tips I Wish I Knew 5 Years Ago
- Sending Data to Excel using Access SQL
- Using Access to Combine Multiple Excel Files: Method 2
- Transposing a Dataset in Access
- Sending Variable Access Data To An Excel Range
- Running an Access Parameter Query from Excel
- Documenting Access Queries in Excel
- Easily Move Access Query Results to Excel
- Clearing Access ImportError Tables
I find a bit of Tipp-Ex gets rid of those pesky rouge objects
On a more serious note, you set xlwkbk to nothing but this isn't referenced anywhere in your code. Is it redundant or is there a missing line?
Also, as the xl object's scope is limited to the Sub it should die gracefully when the Sub exits, but as you say there seems to be this weird voodoo curse that compels VB people to explicitly tidy up such variables.
Eric Lippert has a few thoughts on the origin and necessity of this practice: http://blogs.msdn.com/b/ericlippert/archive/2004/04/28/122259.aspx
Gordon: Thanks for catching those two mistakes with that giant eye of yours.
Fixed.
Is the code above now with the corrected info for copy and paste or do I look else where for it?
Fred: the code is good
"I first want to point out that this code can also come in handy if you need to run an external Excel macro in another workbook. That is to say, while you are working in an Excel file, you can reach out and run a macro in another workbook."
This code will only run a macro that exists in the step 1 file, in this example "C:\Book1.xlsm"
I need to be able to run a macro from one Excel workbook on a worksheet in a different Excel workbook. If I open the workbook I need to run the macro on, it can't find the macro. If I open both workbooks, it either can't find the macro, or only runs it against the workbook with the macro in it, which does me no good atall... /cry
I'm an Access guy; this Excel stuff is making my ears bleed. Oh well, thanks anyway...
These tools have helped me a lot in automating email. Thanks Mike
Thanks, that was the trigger I needed to let Excel create a graph in existing WB (with macro in WB), save as pict. to be used again in Access. My problem solved.