Access 2000 and pivot tables

To transfer data to Excel...

Option 1
docmd.TransferSpreadsheet acExport, ...

Option 2
Once you have made sure that the spreadsheet name does not exist in an Excel.Application object that you dimension and use to open your workbook...


sInsert = "SELECT * INTO [Excel 8.0;DATABASE=" & _
sFileName & ";HDR=Yes;IMEX=0].[" & sSpreadSheetName & "] FROM MyQueryName"

If Len(strFilter) > 0 Then
sInsert = sInsert & " WHERE " & strFilter
End If

CurrentDb.Execute sInsert

Option 3

Docmd.OutputTo acOutputQuery, "QueryName", acFormatXLS, ...


Option 4
Use DAO to dim a rs object, and an Excel.Application object to get at the CreateFromRecordset method

dim rsf as dao.recordset

set rsf = currentdb.Querydefs("QueryName").OpenRecordset

Set xlWS = objExcel.Worksheets.Add
xlWS.Name = "SheetName"
Set xlWR = objExcel.Worksheets(xlWS.Name).Range("A1")
For lCols = 0 To rs.Fields.Count - 1
xlWR.Offset(0, lCols).Value = rsf.Fields(lCols).Name
Next lCols
xlWS.Range(xlWS.Cells(1, 1), xlWS.Cells(1, rsf.Fields.Count)).Font.Bold = True
xlWS.Range("A2").CopyFromRecordset rsf

There are a lot of ways to get information to Excel. The methods listed here have extra functionality that you might explore in the help file.

By the way, have you looked at a Crosstab Query for your pivot table needs? Just a thought. Create the CT query first, then output it to Excel.

Anyway, HTH