(Solved) – Run EXCEL VBA in ACCESS


I have been researching this at a great deal and I am not really finding any leads to how this would work. I have written a module in excel that I want to run in Access. I have pasted the code I wish to run in access. All the examples or information I have found that is from 2006 access. I am using 2016 Access and the code does not work.

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").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"


    ' Application.Quit
    Application.DisplayAlerts = True

End Function

Code I have found and tried to use it in access to help me run this 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)
    End With
    Set XL = Nothing
End Function

Leave a Reply

Your email address will not be published. Required fields are marked *