Macro 93: Running an Access Macro from Excel
You can run Access macros from Excel, using automation to fire the macro without opening Access.
This technique can be useful not only for running those epic macros that involve a multistep series
of 20 queries, but can also come in handy for everyday tasks like outputting a Access data to an
Excel file.
How it works
The following macro is a simple way to trigger an Access macro programmatically.
Note that you will need to set a reference to the Microsoft Access Object Library. To do so, open the
VBE in Excel and select Tools➜References. The Reference dialog box opens. Scroll down until you
find the entry Microsoft Access XX Object Library, where the XX is your version of Access. Select the
check box next to the entry.
Sub Macro93()
‘Step 1: Declare your variables
Dim AC As Access.Application
‘Step 2: Start Access and open the target database
Set AC = New Access.Application
AC.OpenCurrentDatabase _
(“C:\Temp\YourAccessDatabse.accdb”)
‘Step 3: Open the target report and send to Word
With AC
.DoCmd.RunMacro “MyMacro”
.Quit
End With
End Sub
1. The first thing the macro does is declare the AC object variable. This variable exposes the
Access database application library.
2. Step 2 uses the AC variable to start a new instance of Microsoft Access and open the data-
base that houses the target macro.
3. Step 3 runs the appropriate macro and closes the database.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code into the newly created module.
