Writing or Running an Excel Macro upon Export

[Guest]

I am currently using DoCmd.TransferSpreadsheet acExport to export a query
into Excel format. Obviously, this does not format any of the data. I also
have an Excel module (*.bas file) that I would like to either run
automatically, or somehow be able to write the procedure into the file's
module.

The perfect end result would be to attach the *.bas file to the Excel file,
and include a small bit of code in the Workbook_Open event handler procedure
to call this macro.

Any ideas?

TIA

Jason

 

[Guest]

Hi Jason,

How about creating an Excel workbook that contains the module and macro, and
using this as a "template". Air code:

Dim DestinationFile As String
Dim TemplateFile As String

TemplateFile = "D:\Folder\ExportTemplate.xls"
DestinationFile = "D:\Output\Filename.xls"

FileCopy TemplateFile, DestinationFile
DoCmd.TransferSpreadsheet...

 

[Guest]

Hi, John. Thanks for a great suggestion. I will try it out and let you know
whether it is working for me.

Jason

 

[Guest]

Thanks again for the input, John. We got it working using a template with
the macro attached.

Jason