Exporting Query To Excel Template -How to determine sheet to export to

[dominicdunmow]

I'm using the following code to export a query to an excel template. The code works, but my problem is that it adds a new sheet to the template titeld with the query name rather than use the sheet that I have preformatted.

Sub Command5_Click()
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, 8, "1 Optical By Probability", "D:\Global Services Opportunity Pipeline\Reports\Optical By Probability Report.xlt", False
End
Err_Command1_Click:
Kill "D:\Global Services Opportunity Pipeline\Reports\Optical By Probability Report.xlt"
DoCmd.SetWarnings True
End Sub

Can anyone help with the code that determines the sheet name which access will export to.

Thanks.

 

[TomHW]

As far as I know, you cannot choose a sheet to export to through the TransferSpreadsheet command.

You should try automation if you need to have that much control over where the data goes and how it is formatted. If you set a reference to 'Microsoft Excel X.0 Object Library' you can then easily manipulate an excel application and its entire object model through Access.

Tom