Access 2000 and pivot tables

[jsilveira]

Hi,

I currently have an access table that I would like to export to excel and automatically create a pivot table ( this will hopefully be done from the table's form, by clicking one Export button)I was wondering whether anyone knows how this can be accomplished? Or if you can guide to some kb's where i can find the info.

Much appreciated,
JS

 

[rubbernilly]

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