Apologies if this appears twice but my first posting seems to have got
lost..


I am attempting to create a pivot table in Excel. (Aside: I cannot
get the Pivot table Form Wizard in Access to work, so am exporting to
Excel and attempting to create it there).

I have:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"StockPlantPlanQuery", fileName

Dim excelobj As Object
Set excelobj = CreateObject("Excel.Application")
Set excelobj = openExcel
excelobj.Workbooks.Open fileName
excelobj.Visible = True

With excelobj
.ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base,
SourceData:= _
"StockPlantPlanQuery!C1:C4").CreatePivotTable
TableDestination:=.Range("G7"), TableName:="PivotTable"
.ActiveSheet.PivotTables("PivotTable").SmallGrid = False
.ActiveSheet.PivotTables("PivotTable").AddFields
RowFields:=Array("Stock Plant", "Plant"), ColumnFields:="Stick week"
.ActiveSheet.PivotTables("PivotTable").DataFields
ColumnFields:="URC"
.ActiveSheet.PivotTables("PivotTable").PivotFields ("URC").Orientation
= xlDataField
End With

The data is transferred and the Pivot table is created but the code
fails on the first line of ...Addfields with the unhelpful message:
Runtime error 1004
Addfields method of PivotTable class failed.

I have created the code in Excel by recording a macro and have copied
it
across to VBA in Access. I thought the problem might be the Array, so
simplified this by having
only one row field but had the same error.

Any help would be appreciated.