Suggest a loop through all fields, regardless of their location in the table. It seems to me that this would be faster than traversing the entire range, removing duplicates and sorting the list of column items.
the Entries of (Row|Column|Page)field are unique by Implementation of Pivottables, the Entries in Datafield are calculated by some Aggregate-function … seems it doesnt make sense to grep for uniques??
Any field can be used as data field. But I only want the field items. Just a pivot table helps you quickly get a list of items.
what?? You lost me!
@karolus, I don’t quite understand you…
This is what I want 
Will it be so easy I don’t know yet
''' Excel VBA
Worksheets("sheet4").Activate
With Worksheets("sheet3").PivotTables(1)
c = 1
For i = 1 To .PivotFields.Count
r = 1
Cells(r, c) = .PivotFields(i).Name
r = r + 1
For x = 1 To .PivotFields(i).PivotItems.Count
Cells(r, c) = .PivotFields(i).PivotItems(x).Name
r = r + 1
Next
c = c + 1
Next
End With
UPD 1:
There is a property: DataPilotFields, but Xray shows its empty structure (no element names). But it seems that this is the analogue of the Excel PivotFields property.
UPD 2: It works (!)
Dim i%, s$
oTable = oSheet.DataPilotTables(0)
s = "Pivot Fields:"
For i = 0 To oTable.DataPilotFields.Count - 1
s = s & Chr(10) & oTable.DataPilotFields.getByIndex(i).Name 'ElementNames(0)
Next
MsgBox s
oTable.DataPilotFields.getByIndex(i).Items.ElementNames structure contains field items. Solved.
The following macro shows the elements of the row fields of the pivot table, which is located on the second sheet of the document.
Sub ShowRowFieldItems
Dim oPivotTable, oPivotField, oPivotItem, s As String
oPivotTable=ThisComponent.Sheets(1).DataPilotTables(0)
For Each oPivotField In oPivotTable.RowFields
s="Row field: " & oPivotField.Name
For Each oPivotItem In oPivotField.Items
s=s & Chr(10) & oPivotItem.Position & ":" & oPivotItem.Name
Next oPivotItem
Msgbox s
Next oPivotField
End Sub
@sokol92, DataPilotFields property contains all fields (Row|Column|Page|Data Fields). By analogy with Excel, and also simple.
I deliberately gave an example for row fields (in Excel, the analogue: RowFields).
The text of the macro looks almost the same as for VBA.
I will give the final version of the procedure:
Sub DumpItemsOfDataPilotFields()
''' Dump items.
Const MAXITEMCOUNT = 12
Dim oSheet As Object, oTable As Object
Dim i&, j&, c&, r&, s$
oSheet = ThisComponent.Sheets(5) 'your target sheet for data output
oTable = ThisComponent.Sheets.getByName("Pivot Table").DataPilotTables(0) 'only one table on this sheet
With oTable
Rem .refresh 'necessary, but takes some time
s = "DataPilot Name: " & .Name & Chr(10)
s = s & "List of Fields (" & .DataPilotFields.Count & "):" & Chr(10)
For i = 0 To .DataPilotFields.Count - 1
s = s & Chr(10) & .DataPilotFields(i).Name
Next
MsgBox s, , "Information"
c = 0
For i = 0 To .DataPilotFields.Count - 1
r = 0
oSheet.getCellByPosition(c, r).String = .DataPilotFields(i).Name
r = r + 1
For j = 0 To .DataPilotFields(i).Items.Count - 1
oSheet.getCellByPosition(c, r).String = .DataPilotFields(i).Items(j).Name
r = r + 1
If r = MAXITEMCOUNT And r < .DataPilotFields(i).Items.Count Then
oSheet.getCellByPosition(c, r).String = "..."
Exit For
End If
Next
c = c + 1
Next
End With
End Sub 'DumpItemsOfDataPilotFields
Some fields do not need to be displayed in full (e.g., date, cost, etc.). You can limit the output of such fields by specifying a value for the constant MAXITEMCOUNT. Instead of continuing, you will see “…” on the last row. If you see a continuation symbol in a field of interest, increase the MAXITEMCOUNT value.
Where is it used? I use it for data validation dropdowns.
Youre kidding us?? It just dumps (parts) of the original Datasource to Sheet6 nothing else… so may I ask, whats the advantage??
In no case. These are unique item lists sorted in ascending order. Filter field dropdowns fetch data from here.
I have not provided complete data. But you can see… Some fields (Date, Incident Cost) are cut off, as I wrote above, they are not needed. Something like that.
@karolus, you have a very high qualification, I think you are kidding.
In general, I am developing an educational application on which you can learn, as well as compare various data analysis tools (there are a lot of subtle differences), and use it as a working layout for developing typical databases in Calc based on it without using Base. And the community helps me with this. Someday I'll post it for everyone to see...
Some screenshots
For example, this issue was clarified in the process of work…
@eeigor Hereis my Testscenario, the Sourcedata:
The Pivottable grouped by Month:
And the Output produced by your Basiccode:
Indeed, they are unique and sorted.
@karolus, it’s simple: this is the data (presented by you) that cannot be used as a filtering criterion. E.g., for the Date field (between start and end), I use a different approach: I enter, but I do not choose (see screenshot above).
And the autofilters of the pivot table will be populated with all this data. By the way, they can be grouped (for dates), etc.
UPDATED:
@Villeroy is right: this is too difficult for a beginner. To start working right away, you need a sufficiently high qualification. Or someone else has to customize, automate the application.
I tried to distinguish a class of problems that are more efficient to solve using the Calc database, and write a typical code for this, as well as suggest an interface. There is only one table in my application, and the primary key is not needed. But all the data analysis tools are used to compare and learn how to work with them.
However, automation with an advanced filter is so easy and the use of this filter is so effective that it is surprising that it is still not used by everyone. Unless you think using regular expressions is a major obstacle to working with a filter (it is possible without them).