exporting to excel - excel macro automatically runs after export!!?

[paul donnelly]

Hi Guys,

I am exporting a table to excel by an access 2002 macro,
this works fine what I would like to do is as soon as the
access data is exported that an Excel Macro that I have
alrady made runs automatically to format the exported
table.

Is this possible?

 

[Ken Snell]

Not via a macro in ACCESS....you need to use VBA code and automation to do
what you seek.

In VBA, you could do something similar to this:


Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "Ken.xls"
strMacro = "Testing1"
' Export the table
DoCmd.TransferSpreadsheet acExportDelim, , "TableName", "C:\" & strFile
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
End Sub