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

Sepop

New Member
Joined
Jun 30, 2014
Messages
22
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

TerryHogarth21

Board Regular
Joined
Mar 20, 2012
Messages
245
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,186,559
Messages
5,958,489
Members
438,361
Latest member
mihi78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top