How to Run an Excel Macro Automatically After an Access Export
Question details
The user needs a method to automatically trigger a specific Excel macro, which is stored in the Personal Macro Workbook (Personal.xlsb), immediately after an Access database finishes exporting a query to Excel.
- Product
- Microsoft Access and Excel
- Device & OS
- not provided
- Scenario
- Exporting data from a Microsoft Access query to an Excel workbook and seamlessly formatting or processing the resulting file using an existing VBA macro.
- Observed behavior
- The exported Excel workbook opens successfully, but the macro does not run automatically, forcing the user to rely on a manual keyboard shortcut.
Ensure that your Personal Macro Workbook (Personal.xlsb) is configured correctly in Excel and that the macro you intend to run is defined as a public subprocedure within a standard module (e.g., Module31).
Use Access VBA with Application.Run to Trigger the Macro
You can control the Excel application directly from your Access VBA code using COM automation to run the macro immediately after the data export finishes.
By modifying the code behind your Access export button, you can create an instance of Excel, open the exported workbook, and directly call the macro stored in your Personal.xlsb file.
Open your Access database, switch to Design View on the form containing your export button. Right-click the button, select 'Build Event...', and open the VBA Editor for the On Click event.
At the beginning of your subroutine, declare variables for the Excel application and workbook by typing: Dim xlApp As Object and Dim xlWB As Object.
After your export command (such as DoCmd.TransferSpreadsheet), create the Excel instance by adding: Set xlApp = CreateObject("Excel.Application") and make it visible with xlApp.Visible = True.
Open the newly created Excel file by adding: Set xlWB = xlApp.Workbooks.Open("C:\Path\To\YourExportedFile.xlsx").
Execute the macro stored in your Personal workbook by adding: xlApp.Run "PERSONAL.XLSB!Module31.MyMacro". Ensure you replace 'Module31' and 'MyMacro' with your exact module and macro names.
Use an Excel Template with a Workbook_Open Event
If coding Access VBA automation is too complex, you can export the data into a pre-formatted macro-enabled Excel template that triggers the code automatically upon opening.
Manage Macros and Spreadsheets seamlessly with WPS Office
While Microsoft Access handles the database exports, you can use WPS Spreadsheet to write, manage, and execute your VBA macros on the exported data. WPS Office provides a highly compatible, lightweight, and free alternative for complex spreadsheet automation tasks.
- 1. Install WPS Office: Download and install WPS Office Free from the official website.
- 2. Open Your Exported Data: Launch WPS Spreadsheet and open the Excel file (.xlsx or .xlsm) you exported from your database.
- 3. Run Your Macros: Navigate to the Tools tab on the top ribbon and select 'Macros' to seamlessly edit or execute your VBA code.
Frequently Asked Questions
Why do I get an error saying the macro cannot be found after exporting?
This usually happens if the Personal Macro Workbook (Personal.xlsb) is not open in the background, the macro name is misspelled, or the macro is marked as Private. Verify the exact path string in your VBA code, which should look like 'PERSONAL.XLSB!ModuleName.MacroName'.
Can I run an Excel macro from Access without showing the Excel application window?
Yes. In your Access VBA script, you can set xlApp.Visible = False to run the Excel processes entirely in the background. Just remember to save the workbook, close it, and explicitly use xlApp.Quit at the end of your code to prevent invisible background processes from hanging your system.
How do I find the correct module name for my Excel macro?
Open Excel, press Alt + F11 to launch the VBA Editor, and look at the Project Explorer pane on the left side. Expand 'VBAProject (PERSONAL.XLSB)' and then open the 'Modules' folder to see the exact module name (e.g., Module1, Module31) containing your script.
