How to Change the File Name for an Export Query as Excel Macro

[GeyikBaba]

In Access 2003, I have a macro which generates and saves an Excel
spreadsheet using a select query. The spreadsheet is saved in the default
output directory, the name of the file is the same as the name of the query.
So if the query is named 'query1', the spreadsheet will be named
'query1.xls'.

I had a problem yesterday where a user tried to generate two spreadsheets,
one after the other. Of course, the second 'query1.xls' overwrote the first
'query1.xls'.

Right now, I am executing the macro as follows:

DoCmd.OPenMacro "macroname"

Is there any way I can expose the macro in VB and provide it with a custom
name for the spreadsheet?

Many thanks
Mike Thomas

 

[GeyikBaba]

Many thanks Andy,

That's exactly what I was after.

Mike Thomas