Setting the Page grouping in an Excel pivot table

[lordfidan]

I have written code to create an excel pivot table, and am trying to set the page value to one of the values in the data, but it keeps telling me that it either can't change to _default or if I try another way, that it has no access to the PivotItems.

I've tried various combinations of:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Datasource'").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Service Name", _
ColumnFields:="Location", PageFields:="Service group"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Annualised Cost")
.Orientation = xlDataField
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Service group"). _
CurrentPage = rstSource!vendor
---------------------------------
and
---------------------------------

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Service group")
Set rstSeeNone = CurrentDb.OpenRecordset("SELECT DISTINCT Vendor FROM [mydata];", dbOpenDynaset)
While Not rstSeeNone.EOF
.PivotItems(rstSeeNone!vendor).Visible = False
rstSeeNone.MoveNext
Wend
.PivotItems("(blank)").Visible = False
.PivotItems("I want to see").Visible = True
End With

Anybody got any ideas?

Thanks.

 

[PHV]

You have to fully qualify ALL the excel objects with your instantiated Excel.Application name (say objXL), e.g.:
objXL.ActiveSheet.PivotTableWizard TableDestination:=objXL.ActiveSheet.Cells(3, 1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886