Export query to a specific sheet/tab in an Excel spreadsheet

[Eddy Sincere]

I may have already posted this question, but when I go to my profile it's not showing up. If I already have please disregard this one. Anyway, I need to export a query to excel (which is working alright). However, what I really need is to export to a specific sheet/tab within the spreadsheet. UNfortunately, I don't know the specific syntax to use and I'm having trouble finding it. Here's the VBA I'm using for the export (Yes, I converted it from a macro. I'm still new at this whole VBA thing):

Private Sub Command0_Click()
On Error GoTo Command0_Click_Err

DoCmd.OutputTo acOutputQuery, "qryExprd", "ExcelWorkbook(*.xlsx)", "C:\Users\XXX\XXX\Database\ControlledDocs\Test.xls x", False, "", , acExportQualityPrint



Command0_Click_Exit:
Exit Sub

Command0_Click_Err:
MsgBox Error$
Resume Command0_Click_Exit

End Sub


Can I just add the sheet/tab name at the end of the file path? Something like: Test.xlsx.Sheet 1

[June7]

AFAIK, that won't work. Can't use OutputTo nor TransferSpreadsheet for that. Open Excel objects in VBA and manipulate the objects. Review http://www.accessmvp.com/KDSnell/EXC...m#WriteRstFile

[Eddy Sincere]

As usual, this is the perfect solution. Thanks for the reference. I plugged in the code, made a few changes and it's working perfectly. Problem solved.