Access VBA to run an Excel Macro from Access

Access VBA to run an Excel Macro from Access

This VBA will run an Excel macro from Access.  The VBA exports a designated Access table to Excel and then runs a macro from Excel.  Allows you to manipulate data from an Access table using an Excel macro.  Then you can have the macro save the file, write back to the Access Table or anything else you can think of.

*Update the MySQL SELECT statement with your table name.

*Update MySheetPath with your Excel file

* Update Set XlSheet = XlBook.Worksheets(“MasterList”) with the Excel Sheet name where you want to import your Access table to.

*Update Xl.Run (“ExcelMacroName”) with the name of the macro you want Access to run.

Dim MyRecordSet As New ADODB.Recordset
Set cnn = CurrentProject.Connection
MyRecordSet.ActiveConnection = cnn

MySQL = "SELECT Table1.* FROM Table1;"
MyRecordSet.Open MySQL

MySheetPath = "Z:\My Documents\FileName.xlsm"
MsgBox "Please open Excel. Once open, press the OK button below", vbOKOnly, "Open Excel"
Set Xl = GetObject(, "Excel.Application")
Set MyCurrentDb = CurrentDb
Set XlBook = GetObject(MySheetPath)

Xl.Visible = True
XlBook.Windows(1).Visible = True

Set XlSheet = XlBook.Worksheets("MasterList")

XlSheet.Range("A2").CopyFromRecordset MyRecordSet

XlBook.Activate

Xl.Run ("ExcelMacroName")

XlBook.Close (False)