I am trying to automate in Access 2003 exporting a pivot table to Excel 2003 and retaining the pivot format.
Basically, I am trying to automate the manual process of:
1) opening a query in Access in pivot table view,
2) selecting all (Ctrl A) then
3) pasting into Excel.
I tried the following but it copies the raw data into Excel and loses the pivot formatting:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qTest", "c:\test.xls", False
I also tried the following the code (see code section) from another EE question (#21973564) and I'm getting the following errors:
WB.Columns.AutoFit -> Object doesn't support this property or method
).Refresh ->Unable to get the PivotTables property of the Worksheet class
Are these errors caused by the fact that it was written for excel 2000 and I'm using Excel 2003?
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qTest", "c:\test.xls", False
'Declare and Start Microsoft Excel 2000.
Dim AppExcel As Object, WB As Object
Set AppExcel = CreateObject("Excel.Application")
Set WB = AppExcel.Workbooks.Open("c:\test.xls")
AppExcel.Visible = True
'Quit Microsoft Excel and release the object variable.
Set AppExcel = Nothing
Set WB = Nothing