Cross tab export to Excel

[tmb904]

anyone have a good way of exporting a crosstab from Access to excel thru code?

I would need the crosstab to sum on the bottom and right hand side.


In the past, I have accomplished this by just transferspreading my data to a tab with an updatable pivot table in the excel file. The reason I am looking for an alternative is because sometimes I exceed 65K lines and the file is too large with the pivot table to send out thru email.

 

[tranchemontaigne]

(1) write a cross tab query that sums accross each row
(2) write a SELECT query witha GROUPBY clause that will summ each field
(3) write a UNION query to combine the results of steps (1) and Step (2)
NOTE: you will want to include a static value field (e.g. Expr1: 'A') at the beginning of step (1) and again in step (2) (e.g. Expr1: 'B'). This will allow you to control the way the UNION query assembles the results of the 2 source queries
(4) use any of the standard methods for transferring data from MS Access to MS Excel. The best resource I've found for this task is http://www.zmey.1977.ru/Access_To_Excel.htm
NOTE: The simpliest and most error free method is to use a code instruction similar to
docmd.outputto acoutputquery, [union query name wrapped in double quotes], acformatXLS, [directory path where MS Excel file should be created wrapped in double quotes]
Personally I like the ADODB approach so you can set the cell on the MS Excel spreadsheet where the query output should begin. This allows me to manually set report headers and then create heirarchical column headers.
________
VAPOR GENIE VAPORIZER