Run Excel VBA in Access

Question

I have been researching this a great deal and I am not finding any leads to how this would work.

I have written code in Excel that I want to run in MS Access. I have pasted the code I wish to run in Access.

All the examples or information I have found is from 2003 Access. I am using 2016 Access.

The Excel code

Public Function getworkbook()
    ' Get workbook...
    Dim ws As Worksheet
    Dim Filter As String
    Dim targetWorkbook As Workbook, wb As Workbook
    Dim Ret As Variant

    Application.DisplayAlerts = False

    Sheets("DATA").Delete
    '   Sheets("DATA").Cells.Clear

    Set targetWorkbook = Application.ActiveWorkbook

    ' get the customer workbook
    Filter = "Text files (*.xlsx;*.xlsb),*.xlsx;*.xlsb"
    Caption = "Please Select an input file "
    Ret = Application.GetOpenFilename(Filter, , Caption)

    If Ret = False Then Exit Function

    Set wb = Workbooks.Open(Ret)

    wb.Sheets(1).Move After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)

    ' ActiveSheet.Paste = "DATA"

    ActiveSheet.Name = "DATA"

    ThisWorkbook.RefreshAll

    ' Application.Quit
    Application.DisplayAlerts = True

End Function

Code I found and tried to use 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

Answer 1

First, to clear up terminology:

• VBA is a separate language and not tied to any MS Office application. Under Tools\References, you will see Visual Basic for Applications is usually the first checked object. What differs between running VBA inside Excel, Access, Word, Outlook, etc. is the default access to their object library. Specifically:

  • Only Excel sees Workbook, Worksheet, etc. without defining its source
  • Only Access sees Forms, Reports, etc. without defining its source
  • Only Word sees Documents, Paragraphs, etc. without defining its source

• When running a foreign object library inside an application, such as MS Access accessing Excel objects, you must define and initialize the foreign objects via reference either with early or late binding:

  ' EARLY BINDING, REQUIRES EXCEL OFFICE LIBRARY UNDER REFERENCES
  Dim xlApp As Excel.Application
  Dim wb As Excel.Workbook
  Dim ws As Excel.Worksheet
  
  Set xlApp = New Excel.Application
  Set wb = xlApp.Workbooks.Open(...)
  Set ws = wb.Worksheets(1)    
  
  
  ' LATE BINDING, DOES NOT REQUIRE EXCEL OFFICE LIBRARY UNDER REFERENCES
  Dim xlApp As Object, wb As Object, ws As Object
  
  Set xlApp = CreateObject("Excel.Application")
  Set wb = xlApp.Workbooks.Open(...)
  Set ws = wb.Worksheets(1)  
  

With that said, simply keep original code nearly intact but change definitions and initializations. Notably, all Application calls now point to Excel.Application object and not to be confused with Access' application. Plus, best practices of avoiding .Select/ .Activate/ Selection/ ActiveCell/ ActiveSheet/ ActiveWorkbook.

Public Function getworkbook()
    ' Get workbook...
    Dim xlApp As Object, targetWorkbook As Object, wb As Object, ws As Object         
    Dim Filter As String, Caption As String
    Dim Ret As Variant

    Set xlApp = CreateObject("Excel.Application")
    Set targetWorkbook = xlApp.Workbooks.Open("C:\Path\To\Workbook.xlsx")

    xlApp.DisplayAlerts = False

    targetWorkbook.Sheets("DATA").Delete

    ' get the customer workbook
    Filter = "Text files (*.xlsx;*.xlsb),*.xlsx;*.xlsb"
    Caption = "Please Select an input file "
    Ret = xlApp.GetOpenFilename(Filter, , Caption)

    If Ret = False Then Exit Function    
    Set wb = xlApp.Workbooks.Open(Ret)

    wb.Sheets(1).Move After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)    
    Set ws = targetWorkbook.Worksheets(targetWorkbook.Sheets.Count)
    ws.Name = "DATA"

    targetWorkbook.RefreshAll

    xlApp.DisplayAlerts = True
    xlApp.Visible = True                        ' LAUNCH EXCEL APP TO SCREEN
    ' xlApp.Quit

    ' RELEASE RESOURCEES
    Set ws = Nothing: Set wb = Nothing: Set targetWorkbook = Nothing: Set xlApp = Nothing
End Function

By the way, above can be run in any MS Office application as no object of the parent application (here being MS Access) is used!

Answer 2

There are few concepts you need to deal with first.

Library references and scope

Your original code was written in Excel. Therefore, in that VBA project, it has Excel object referenced. In your Access VBA project, that is not referenced. You can compare this by looking at Tools -> References.

That brings us to the concept of "early-binding" and "late-binding". When you type in things like Range., you get VBA's intellisense to tell you what you can do with a Range or whatever. But in Access, you don't have Excel object library referenced by default. Therefore, Range. will not yield intellisense and you can't run the code because Access does not have Range in its object model and your VBA project mostly likely don't have a reference that has it.

Therefore, your code need to be adjusted to run late-bound if you do not want to add reference to Excel object model, and you most likely do want that anyway.

Unqualified Reference

Your original Excel code contains unqualified references to various global objects that are available in Excel's object model.

Application.DisplayAlerts = False
...
Sheets("DATA").Delete
...
Set wb = Workbooks.Open(Ret)
...

Those won't necessarily work consistently in VBA projects hosted by other hosts other than Excel and most certainly won't work in late-bound code. Furthermore, if you elect to add a reference to Excel's object model, you still end up leaking Excel instance which can cause ghost instances because unqualified references to the global objects will implicitly create an Excel instance that you can't interact and that can also cause other runtime error down the path. To make your code more late-bindable, you need something like:

Set ExcelApp = CreateObject("Excel.Application")

ExcelApp.DisplayAlerts = False
...
Set MyBook = ExcelApp.Workbooks("Whatever")
MyBook.Sheets("DATA").Delete
...
Set wb = ExcelApp.Workbooks.Open(Ret)
...

Note how all global objects that you could have accessed in a Excel-hosted context now have to be a variable on its own. Furthermore, you won't have access to ThisWorkbook or even Sheet1 in other VBA projects because Excel is no longer the host. You must adjust accordingly.

Switching between early-binding & late-binding

Early-bound code makes it much easier for you to develop since you get full intelisense and object browser helping you write the code. However, when referencing other object models, you might want to distribute your VBA code using late-binding to avoid versioning problems and broken references. But you can have best from both worlds:

#Const EarlyBind = 1

#If EarlyBind Then
Dim ExcelApp As Excel.Application
#Else
Dim ExcelApp As Object
#End If

Set ExcelApp = CreateObject("Excel.Application")

This illustrates the use of conditional compilation argument to allow you to have ExcelApp variable that can be either Excel.Application (aka early-bound) vs. Object (aka late-bound). To change, you simply change the Const LateBind line between 0 or 1.