Export to Excel And Run Macro

[nxqviet]

Hi,

I need help with a function that allow me to execute a macro in Excel.

Secifically, I was able to transfer 2 tables from access to Excel. But
this code seem to work only when the target excel file is not open.

What I really need is: Transfer the tables (regardless of the target
file is open or not), then run a macro in that target excel file. How
do i do that?

My question is, if the file is not open. Can I still run the macro? If
not, then how do I open the file after transfer the tables, and before
running the macro. The macro's Name is "Template"

Here is my transfer code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"tblExportPack", "C:\Template.xls", , "Packages"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"tblExportProducts", "C:\Template.xls", , "Products"

Thanks

V

 

[Ken Snell \(MVP\)]

An EXCEL file must be open if you wish to run a macro within that EXCEL
file.

Here is a generic VBA subroutine that shows how to run such a macro:

Public Sub RunAnExcelMacro()
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
End Sub

 

[nxqviet]

Ken,

Thanks for the Code here. I'll try it.

V_

 

[nxqviet]

Ken,

When the code Quit Excel after running the macro. Would it save the
file before closing?

I do Not want it to save, how do I do that.

Thanks,

V_

 

[Ken Snell \(MVP\)]

The code sample I posted does not save any changes made in the EXCEL file:
xwkb.Close False

Change False to True if you do want to save changes.

 

[nxqviet]

I see, Thanks Ken

The code sample I posted does not save any changes made in the EXCEL file:
xwkb.Close False

Change False to True if you do want to save changes.