Run Access VBA from Excel or Run Excel VBA from Access?

[Sepop]

Hi,

I have a report that I am automating. The process consists in an Excel VBA Macro that gives the format to some excel files and saves them in an specific folder for access to take these files and import them into the corresponding tables.

I was able to automate this process through an Excel Macro that does the first part and an Access VBA module that imports the Excel information into the corresponding tables and deletes/appends the information as needed.

What I want to know now is these:

1. How can I connect these two VBA codes (the one from excel that consists in three Macros) and the one in Access? And

2. Is it better to A) run everything form Excel where I can click on the button that runs the macros and make it open the Access file and run the module or B) to run this from an Access form that opens the Excel containing the macro, runs it and then works on the rest of the Access code?

Thanks a lot!
Sebastian

 

[TerryHogarth21]

I personally like running everything in Access since that's my final destination of where the data goes and is managed by forms and reports etc.

Here's something that you could use to adapt that in essence in Access opens up an excel file, runs the macro, then afterwards import that excel file into a table in access.

Option Compare Database
Sub OpenFileRun()

'Must reference Excel Object Library in Tools->Reference
Dim xlApp As Excel.Application
Dim fName As String, fCode As String
Set xlApp = CreateObject("Excel.Application")

fName = "YourFilePath"
fwbname = "YourFileNamewithExtension"
fCode = fwbname & "!YourMacro"

xlApp.Visible = True

'Open the file
xlApp.Workbooks.Open fName, True, False

'run your macro
xlApp.Application.Run (fCode)

'close activeworkbook
xlApp.ActiveWorkbook.Close (True)

'quit Excel instance
xlApp.Quit

Set xlApp = Nothing

'put your transfer code below

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "Table1", fName, True, "Data!A1:T2000"

End Sub

 

[Sepop]

Here's something that you could use to adapt that in essence in Access opens up an excel file, runs the macro, then afterwards import that excel file into a table in access.

Thanks! worked perfectly!
S