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
--
Peter Schmidt
Ross-on-Wye, UK

On Thu, 10 Feb 2005 08:19:04 -0800, "Pete"
<(E-Mail Removed)> wrote:

>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
---
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW