Access VBA Create Pivot table in Excel

[tomlon]

I have a query (qry123) that I have a pivot table form based off. I need VBA that will export this query to Excel. This is not the issue, I can use DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "YTD Data", strFilePath, True command and expor tthis query to a new Excel workbook. What I need is to have code that will change this raw data into a pivot table. Again, this needs to be done from Access. I would like to do this with late binding.

 

[DavidAtWork]

This can be done after the qry has been exported to Excel, by using this methodology
first open an instance of Excel,
open the workbook you've just created
go to cell A1 of the worksheet and use xlDown and xlRight to establish the range of your pivot table
try recording a macro that creates the pivot table you require and introduce a variable for the range. This range can be set by the previous step above.
Save the workbook, Kill Excel

There are recent posts on how to start an instance of Excel/Open workbook

David