DoCmd.Output

[LEXCERM]

Hi all.

I am currently using the following line to output data to Excel:-

DoCmd.OutputTo acOutputQuery, "qry_OutstandingRecs", acFormatXLSX

This will open a dialog box and you select a path/directory for saving.

Is there a way that you can capture the name of the file/path in a variable then use automation to open the saved file for editing?

I am very familiar with using the Excel object automation to do this, but am just putting this out there to see if it can be done when using the DoCmd.Output method as this seems much cleaner.

Thanks in advance.

 

[Minty]

Use TransferSpreadsheet if your source is a query and you can specify the file name;
I have a list box with saved queries as reports you can select (me.lstQueries) , strSaveFile is the variable with the file name you want.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Me.lstQueries, strSaveFile, True, , True