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").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 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) .Quit End With Set XL = Nothing End Function