MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

populate collection or array with list of items in pivot pagefields

  Asked By: Holly    Date: Oct 11    Category: MS Office    Views: 1308

In Excel2000, I want to populate a Collection or an Array with all
unique pagefields from a pivottable, so I later can populate a list or
combobox with all items in the collection or array.

What I had in mind is something like:

Sub FindPageFieldItems()
Dim PT As PivotTable
Dim Item As PivotItem (pagefield??)
Dim PageList As New Collection
Dim Count As Integer

Count = 1

Set PT = ActiveSheet.PivotTables("RevenuePivot")
For Each PItem In PT.PageFields("GRPTEXT").PivotItems(?)
Set PageList(Count) = current pagefield (?)
Count = Count + 1
End Sub

Alternatively I am considering finding all unique records in the
source access query (output), and then transfer them to a
listbox/combobox...again not sure exactly how to go about this.

I have the connection to the established to the file Data.mdb by using

Set PTCache = ActiveWorkbook.PivotCaches.Add(xlExternal)

DBFile = ThisWorkbook.Path & "\Data.mdb"
Constring = "ODBC;DSN=MS Access Database; DBQ=" & DBFile

QueryString = "SELECT * FROM OUTPUT"

With PTCache
.Connection = Constring
.CommandText = QueryString

And then I build a pivottable based on above.

But are there any suggestions on how I could pull in just the distinct
records from GRPTEXT in OUPUT by using something like


...and add to listbox or collection/array

Anyhow, I am not sure if I'm totally of base here, maybe there is a
much easier method to fill a listbox with all items in from a pivot
pagefield. Any help/guidance would be greatly appreciated!!



No Answers Found. Be the First, To Post Answer.