Macro to get results from Pivot Table / Data Pilot

[mark_t]

Is there any method to get the results from a pivot table / data pilot within a basic macro other than to parse the cell range of the worksheet?

I can use the worksheet function GETPIVOTDATA in a cell formula, but don’t seem to be able to pass a reference to a cell or range within the pivot table in order to call GETPIVOTDATA using “com.sun.star.sheet.FunctionAccess”.

I’ve been searching for an equivalent to the excel VBA pivot table method “GetPivotData”, but unable to find an equivalent for LibreOffice.

[PYS]

Hi

If you need more direct access to the result, you can simply insert the function in a work cell (you can then delete it).

Here the work cell is L1, the data pilot is in E3, you search for Amount total

oSheet = thiscomponent.sheets.getByName("Sheet1")
oCell = oSheet.getCellRangeByName("L1")

with oCell
	.formula = "=GETPIVOTDATA($E$3;""Amount"")"
	msgbox "Result: " & .value
end with

[EDIT]
correction code

Regards

[mark_t]

Thanks, may be a usable workaround to pick values from the pivot table, I was hoping for a method that could be used directly from the pivot table object in the macro but it doesn’t seem to be available from the api.

[karolus]

Hallo

Start with mri|xray on the sheetobject, quick try from IPython notebook:

doc = XSCRIPTCONTEXT.getDocument()
pivots = doc.CurrentSelection.Spreadsheet.DataPilotTables #Container of Pivotables
mypivot = pivots.getByName('DataPilot1') # one of them by Name
dt = mypivot.OutputRange # addressobjekt
datarange = doc.Sheets.getCellRangeByPosition(dt.StartColumn,
                                              dt.StartRow,
                                              dt.EndColumn,
                                              dt.EndRow,
                                              dt.Sheet)
print(datarange.DataArray)

[mark_t]

Thanks, I also saw the OutputRange property in OOME, it might help for collecting array of data from simple pivot tables without too many sub sections.

[Hi-Angel]

But this is not only results, it also includes the “technical” rows. Such as, rows where you choose a filter from Page Fields, etc.