Run Excel Macro from Access VBA

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 Access Macro using RunCode

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

Step 2:

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

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

You may also create a Macro in User Interface using RunCode.

Outbound References:

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