How to Run a Macro in Excel From Access

The Microsoft Office suite of programs all utilize a special version of the Basic programming language called Visual Basic for Applications. This programming language can be used to automate tasks inside the programs or give them functionality they were not originally created to have. You can even use it to access files in completely different programs, such as running an Excel Macro directly from an Access database. This can be a useful function to have if your Access database is pulling information from an Excel spreadsheet but you need to frequently update or modify the information in the wpreadsheet for some reason.

Instructions

1. 

   Navigate through your computer's program files and start the Microsoft Access software.

2. 

   Create a new database by clicking on the Microsoft Office logo at the top left corner of the screen and choosing the "Blank Database" option.

3. 

   Click on the "Database Tools" tab, then on the "Visual Basic" button to switch over the Visual Basic view.

4. Make a new function using the opening and closing "Sub" commands. Then declare a new variable that will open Excel by using the "CreateObject" command. Write out the code so it looks similar to "Set apExcel = CreateObject("Excel.Application")"

5. Use the "GetObject" command to open the specific Excel spreadsheet that contains the macro you want to run. Type a line of code such as "Set apExcelsheet = GetObject(C:\SomeSpreadsheet.xls")"

6. Run the macro you have recorded or coded out by typing out a line of code that takes advantage of the "Run" object of the "Application" command. It should look like this: "apExcel.Application.Run "SomeSpreadsheet!YourMacro"

7. Close the Excel program by typing "apExcel.Quit" as the final line of the function.

8. Run the program to make sure it is working properly, then save your new database.

Tips & Warnings

• If you don't want the user to see that Excel is opening, set the "Visible" object to "false" by typing a line of code that reads "apExcel.Visible = False"

• You will receive an error message if you do not include the full and correct path to the Excel spreadsheet file, or if you use incorrect capitalization.

Resources

• Official Microsoft Access web page