Access to Excel template

[thart21]

Can't seem to find a solution to this one-
I have an Excel 2007 workbook with 4 existing sheets that each have a pivot table built off of them.
Single Access (2007) crosstab query that I am filtering from the form (in query Forms!frmMain!cboType) based on 1 of 4 selections corresponding to each Excel sheet.
I would like to export the filtered query results to each of the 4 worksheets, retaining the existing headers in the worksheet.

I have tried using this VBA that I found that is supposed to export the filtered query to a new sheet (DSG_BOP_Rsvd), then copy/paste into the the correct sheet (Rsvd_Date). This seems to have deleted my file for some reason.

Private Sub cmdAvailable_Click()
On Error GoTo Err_cmdAvailable_Click
Dim stDocName As String
stDocName = "DSG_BOP_Rsvd"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, stDocName, "\\filelocation\PINN_Template.xls", True
Set xlObj = GetObject("\\filelocation\PINN_Template.xls")
With xlObj.Application
.Sheets("DSG_BOP_Rsvd").Select
.Range("A:BG").Select
.Selection.Copy


.Sheets("Rsvd Data").Select
.Range("A1").Select
.ActiveSheet.Paste
End With
' DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_cmdAvailable_Click:
Exit Sub
Err_cmdAvailable_Click:
MsgBox Err.Description
Resume Exit_cmdAvailable_Click

End Sub

I've looked at hundreds of posts but can't seem to find something that will work in my situation. Could anyone please tell me if I am on the right track with this code and if I just need to tweak it to get it to work?

Thank you!

[ssanfu]

I've looked at hundreds of posts but can't seem to find something that will work in my situation. Could anyone please tell me if I am on the right track with this code and if I just need to tweak it to get it to work?

Thank you!

If has been a long while since I have imported/exported between Excel and Access. I found this site - maybe it will help.

http://www.accessmvp.com/kdsnell/EXCEL_Export.htm