Exporting query w/ parameter to Excel

[Guest]

Hello,

I have a query that I run from a Command Button on my Switchboard that I
ultimately need to export to Excel. When I click on the Command Button
(using 'DoCmd.OpenQuery'), the prompt appears, and after it is answered by
the user, the query runs fine. But when I go to File... Export... the
parameter prompt appears again before allowing the actual export.

Can I avoid this 're-prompting', or if there is a better way to make sure
the query runs and then exports out to excel, that is even better. It isn't
necessary that the user sees the results of the query run before exporting to
Excel.

Thanks.

 

[fredg]

Hello,

I have a query that I run from a Command Button on my Switchboard that I
ultimately need to export to Excel. When I click on the Command Button
(using 'DoCmd.OpenQuery'), the prompt appears, and after it is answered by
the user, the query runs fine. But when I go to File... Export... the
parameter prompt appears again before allowing the actual export.

Can I avoid this 're-prompting', or if there is a better way to make sure
the query runs and then exports out to excel, that is even better. It isn't
necessary that the user sees the results of the query run before exporting to
Excel.

Thanks.

There is no need to actually run the query.

You can use the Command Button Click event to run the
TransferSpreadsheet method:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"QueryName", "C:\Path toFolder\SpreadsheetName.xls", True

Look it up in VBA Help.