Automating Excel to Create Pivot Table

[Pete]

I have written the following code to export a query from Access 2003 to Excel
2003 and then create a Pivot Table from it (I prefer Excel's implementation
of Pivot Tables to Access'). The part of the code that puts in the data field
and formats it works sometimes, but not others! I thought it was down to the
speed of my machine as sometimes it will work when you export for a second
time (even though Excel is closed on both occasions). I dont' get any error
messages and the rest of the code runs,i.e. it goes on to format the Pivot
Table. (I have created a reference to Excel 11 in Access). I can manually
drag the field into the Pivot Table afterwards without any problem.

'*** START OF CODE ***

DoCmd.OutputTo acOutputQuery, strReport, acFormatXLS, strReport &
".xls", True

Dim xlApp As Excel.Application
Set xlApp = GetObject(, "Excel.Application")
With xlApp
Dim intLastRow As Long, strRange As String
intLastRow = .ActiveSheet.UsedRange.Rows.Count
strRange = "qryStudyRevenueExport!R1C1:R" & intLastRow & "C6"
.ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
strRange).CreatePivotTable TableDestination:="", _
TableName:="PivotTable1",
DefaultVersion:=xlPivotTableVersion10
.ActiveSheet.PivotTableWizard
TableDestination:=.ActiveSheet.Cells(3, 1)
.ActiveSheet.Cells(3, 1).Select
With .ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Customer/Supplier Name")
.Orientation = xlPageField
.Position = 1
End With
With .ActiveSheet.PivotTables("PivotTable1").PivotFields("Study
Code")
.Orientation = xlPageField
.Position = 1
End With
With
..ActiveSheet.PivotTables("PivotTable1").PivotFields("Department")
.Orientation = xlRowField
.Position = 1
End With
With .ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")
.Orientation = xlColumnField
.Position = 1
End With
.ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Revenue (£)"), "Sum of Revenue (£)",
xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of
Revenue (£)")
.NumberFormat = "£#,##0"
End With
.ActiveWorkbook.ShowPivotTableFieldList = False
.ActiveSheet.PivotTables("PivotTable1").PivotSelect "",
xlDataAndLabel, True
.ActiveSheet.PivotTables("PivotTable1").Format xlTable1
End With
Set xlApp = Nothing

'*** END OF CODE ***
Any help appreciated

 

[Andi Mayer]

I have written the following code to export a query from Access 2003 to Excel
2003 and then create a Pivot Table from it (I prefer Excel's implementation
of Pivot Tables to Access'). The part of the code that puts in the data field
and formats it works sometimes, but not others! I thought it was down to the
speed of my machine as sometimes it will work when you export for a second
time (even though Excel is closed on both occasions). I dont' get any error
messages and the rest of the code runs,i.e. it goes on to format the Pivot
Table. (I have created a reference to Excel 11 in Access). I can manually
drag the field into the Pivot Table afterwards without any problem.

try with a few DoEvents