How to use a macro to run a make table query and export to excell

[Veli Izzet]

Hi,

I want to use a button on a form that runs a make-table query, and
exports that table to an excell spreadsheet (including the date in the
name of the spreadsheet)

Thanks for answers

 

[fredg]

Hi,

I want to use a button on a form that runs a make-table query, and
exports that table to an excell spreadsheet (including the date in the
name of the spreadsheet)

Thanks for answers

You can use the TransferSpreadsheet method.
Look it up in VBA help for the various arguments.

To include the date in the spreadsheet's name, you can use something
like this:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"QueryName", "C:\FolderName\SpreadsheetName" & Format(Date,
"mmm-dd-yyyy") & ".xls"

It will create a new Workbook, i.e. "SalesDataAug-14-2005".
The worksheet name will be "QueryName".

 

[Joseph Meehan]

Hi,

I want to use a button on a form that runs a make-table query, and
exports that table to an excell spreadsheet (including the date in the
name of the spreadsheet)

Thanks for answers

I have not tried it in code, but can't you just use a select query
directly without making a table??

 

[Veli Izzet]

Maybe,

but the main problem is exporting to excel.

 

[Douglas J Steele]

Use the TransferSpreadsheet method, specifying the query (rather than the
table) and the filename (using Format(Date, "yyyymmdd") to include the date
in its name)