Ask Your Question

Macro to get results from Pivot Table / Data Pilot [closed]

asked 2016-10-23 01:37:16 +0200

mark_t gravatar image

updated 2016-10-23 01:38:17 +0200

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 "".

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-14 10:49:24.329945

2 Answers

Sort by » oldest newest most voted

answered 2016-10-23 11:53:36 +0200

karolus gravatar image


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,
edit flag offensive delete link more


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.

mark_t gravatar imagemark_t ( 2016-10-24 22:33:11 +0200 )edit

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

Hi-Angel gravatar imageHi-Angel ( 2019-07-22 20:55:15 +0200 )edit

answered 2016-10-23 16:06:16 +0200

pierre-yves samyn gravatar image

updated 2016-10-24 09:21:33 +0200


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


edit flag offensive delete link more


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.

mark_t gravatar imagemark_t ( 2016-10-24 22:36:05 +0200 )edit

Question Tools

1 follower


Asked: 2016-10-23 01:37:16 +0200

Seen: 1,013 times

Last updated: Oct 24 '16