Access Macro to export table to Excel then run Excel macro

C

Craig

Hi

I want my user to just click a button to run an Access macro that exports a
table from the same Access database to an Excel spreadsheet and then have an
Excel macro that I developed run on that exported table....is all this
possible?

Thank you for any assistance!

Craig
 
K

Ken Snell MVP

You can do the export via a macro (using TransferSpreadsheet action), but
I'm not aware of any way to run an EXCEL macro from an ACCESS macro. To run
the EXCEL macro, you'll need to use VBA code in EXCEL. Here is sample code
for how to run an EXCEL macro from ACCESS:

Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "ExcelFilename.xls"
strMacro = "MacroName"
Set xls= CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("C:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pause Macro 1
Call Access macro using Excel VBA 1
Export table to txt file with a variable filename 0
close excel in macro 1
Macro to Export 1
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
Compact & Repair, Open table, close table, then run macro 3
Run Excel Macro from Access 1

Top