This Access tutorial explains how to run Excel Macro from Access VBA, either write Excel Macro directly in Excel or you can write Excel Macro in Access.
You may also want to read:
Run Excel Macro from Access VBA
One of the uses to run Excel Macro from Access is when you use VBA to export tons of Access reports to Excel. By default, exporting Access report as Excel gives you no formatting but plain text. However, it would be convenient if you can export the file out from Access to an Excel template containing Macro, and then tell Excel to run a Macro to format itself. Of course you can export your Access data to a pre-formatted Excel template, but not all formatting can be pre-formatted and your exported data columns are not always continuous.
There are two ways to run Excel Macro from Access:
1) Write a Macro in Excel first, and then call the Excel Macro from Access
2) Write Excel Macro in Access to run in Excel
Run an existing Excel Macro from Access
Suppose an Excel Macro called “Macro1″ is saved in workbook ”RunExcelMacro”, which is a Macro to format worksheet. Now you want to run this Macro from Access.
Step 1: Write a function with 2 parameters in Access:
– Create an Excel Object in Access
– Open the workbook template that contains the Excel Macro
– Run the Excel Macro
– Close the workbook
Function runExcelMacro(wkbookPath, macroName) Dim XL As Object Set XL = CreateObject("Excel.Application") With XL .Visible = False .displayalerts = False .Workbooks.Open wkbookPath .Run macroName .ActiveWorkbook.Close (True) .Quit End With Set XL = Nothing End Function
Write a procedure to call the above function. Run the Access Macro by specifying the Excel path and Excel Macro name.
Public Sub runMacroSub() Call runExcelMacro("C:\runMacro.xls", "Macro1") End Sub
Write Excel Macro in Access
Directly write the Excel formatting function in Access
Public Function runExcelMacro(wkbookPath) Dim XL As Object Set XL = CreateObject("Excel.Application") With XL .Visible = False .displayalerts = False .Workbooks.Open wkbookPath 'Write your Excel formatting, the line below is an example .Range("C2").value = "=1+2" .ActiveWorkbook.Close (True) .Quit End With Set XL = Nothing End Function
Write a procedure to call the above function:
Public Sub runMacroSub() Call runExcelMacro("C:\runMacro.xls") End Sub
You may also create a Macro in User Interface using RunCode.