Ask Your Question
1

Macro to get results from Pivot Table / Data Pilot

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

mark_t gravatar image

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

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
2

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

pierre-yves samyn gravatar image

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

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

edit flag offensive delete link more

Comments

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 +0100 )edit
1

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

karolus gravatar image

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

Comments

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 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 491 times

Last updated: Oct 24 '16