Access Macro to export table to Excel then run Excel macro

  • Thread starter Thread starter Craig
  • Start date Start date
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
 
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
 
Back
Top