Is there a way to copy an Access 2003 pivot table view (used to pivot 206,000 records) to Excel 2003 using VBA?
A:
Not sure if number of records matters unless the result exceeds Excel 2003 column and/or row max.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel11, "qryPivot_Table_Name", "C:\Users\user\Documents\PivotTable.xlsx", True
Jeff O
2010-08-23 01:26:45
Thanks, but doing that exports the actual table (or query) not the pivot), which runs up against Excel 2003's 65,536 row limit. So, I would just like to export the pivot table view, i.e., just the results.
H.Lam
2010-08-23 13:35:16
The query is a pivot type, what else would it export besides the result set of the pivot table query?
Jeff O
2010-08-23 13:55:14
My result set for this pivot is over 200,000 records. I'd like to export the "pivot table view" (with hidden details) -- a summary that is only 40 rows.
H.Lam
2010-08-23 14:27:26
If you write a query that returns 40 rows, that's all that gets exported. You're not going to get around the row limit.
Jeff O
2010-08-23 14:34:19
acSpreadsheetTypeExcel12 will work only in A2007 and A2010, and will create a spreadsheet that Excel 2003 can't open, no?
David-W-Fenton
2010-08-23 20:14:07
@David-W-Fenton - Good catch. Use Excel11 for 2003. There are ways for 2003 to open a 2007 file (2007 Microsoft Office system Compatibility Pack for Excel), but not needed when you can control the version.
Jeff O
2010-08-24 12:24:34
FYI, acSpreadsheetTypeExcel9 is as high as it goes in 2003... Thanks.
H.Lam
2010-08-26 13:13:28