Run Excel Macro from Access VBA

This tutorial explains how to run Excel Macro from Access VBA. You can either write Excel Macro directly in Excel or you can write Excel Macro in Access.

Why do you need to run Excel Macro from Access?

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

Method 1 to run Excel Macro from Access:

Example

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:

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

Step 2:

Write a procedure to call the above function:

Public Sub runMacroSub()
    Call runExcelMacro("C:\runMacro.xls", "Macro1")
End Sub

Algorithm

In Step 1

– Create an Excel Object in Access

– Open the workbook template that contains the Excel Macro

– Run the Excel Macro

– Close the workbook

In Step 2

– Run the Access Macro  by specifying the Excel path and Excel Macro name

Method 2 to run Excel Macro from Access:

Example

Step 1:

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

Step 2:

Write a procedure to call the above function:

Public Sub runMacroSub()
    Call runExcelMacro("C:\runMacro.xls")
End Sub

Outbound References:

http://msdn.microsoft.com/en-us/library/office/gg264813%28v=office.15%29.aspx

 

Run Excel Macro from Access VBA