Pivot table in Excel. Exported data from access as excel sheet.

[Angela]

Hi,

I am looking for a fix in below code, probably addition to it.
I'm trying to export a query from ACCESS 2003 to EXCEL 2003.
I do that each time with the export to excel button available in
access which generates a temp file which I have to save all the time
by naming it into appropriate excel file format.

So far I have managed to export only the data sheet.
I'm stuck with creating the pivot table portion.

These few lines I found while browsing and changed them w.r.t. my
requirements.

I would like to have a pivot table in sheet2 based on data range in
sheet1.

Code is below:
----------------------
Public Sub TransferReport()


Dim varFileName As String


varFileName = "C:\MyFile.xls"


'EXPORT DATA
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Month
End Report", varFileName, False, "Sheet1"


End Sub