Ask Your Question

create pivot table with basic macro

asked 2017-07-25 08:43:54 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.


I have the need to create a pivot table with a macro. I'm quite far. My test macro creates the pivot table with filter and subtotals and so on. But the layout is not as expected. I can adjust it with the data pilot layout dialogue but it is the aim to automate the task. It has something to do with the virtual "data" field (that's the name I gave it). It is none of my columns.

If I drag it in the layout dialogue to "column fields" I get a horizontal orientation of my fields. If drag it in "row fields" I get an vertical alignment.

My questions are: How can I reference this "data" tag from my basic macro? How can I move it between "row fields" and "vertical fields" from my basic macro?

Here is my test macro. Perhaps it is useful for others trying to create pivot table via API:

Sub makePivot

dim doc
doc = thisComponent
dim sheets
sheets = doc.Sheets

dim pivotSheetNameOverview as string

dim oController
oController = ThisComponent.CurrentController
dim oSheetObj
'oSheetObj = oController.ActiveSheet
oSheetObj = Sheets.getByName("Unvollständigmeldungen")

dim DataCellRange
DataCellRange = oSheetObj.getCellRangeByName("Unvollständigmeldungen.A:Unvollständigmeldungen.M")
dim RangeAddress
RangeAddress = DataCellRange.RangeAddress
dim Tables
Tables = oSheetObj.DataPilotTables()   'Tables has all the DataPilot Tables in the Active Sheet

'This part of the code just removes the table if it already exists. Prevents error from running the code several times
If Tables.hasByName(pivotSheetNameOverview) THEN 
End If

dim Descriptor
Descriptor = Tables.createDataPilotDescriptor()      'Descriptor contains the description of a DataPilot Table
Descriptor.setSourceRange(RangeAddress)     'RangeAddress is defined above to cover A1:C10       
Descriptor.ShowFilterButton = True                   'Don't show the Filter Button
Descriptor.IgnoreEmptyRows = True
Descriptor.RowGrand = true
Descriptor.ColumnGrand = false
Descriptor.DrillDownOnDoubleClick = true

dim fields
Fields = Descriptor.getDataPilotFields()          

dim fieldVerursacher
fieldVerursacher = Fields.getByIndex(3)   'The first column of the data range has index 0
fieldVerursacher.Orientation =                                    
Dim generalFunctionSeqAuto(0) 
fieldVerursacher.Subtotals = generalFunctionSeqAuto

dim fieldGruppe
fieldVerursacher = Fields.getByIndex(4)   'The first column of the data range has index 0
fieldVerursacher.Orientation =                                    

'dim fieldDaten
'fieldVerursacher = Fields.getByIndex(4)   'The first column of the data range has index 0
'fieldVerursacher.Orientation =                                    

dim fieldABNummer
fieldABNummer = Fields.getByIndex(0)
fieldABNummer.Orientation =
fieldABNummer.Function =

dim fieldAktiv
fieldAktiv = Fields.getByIndex(12)
fieldAktiv.Orientation =
fieldAktiv.Function =

if(Sheets.hasByName(pivotSheetNameOverview)) then
end if

dim sheet
sheets.insertNewByName(pivotSheetNameOverview, sheets.Count) 
Sheet = Sheets.getByName(pivotSheetNameOverview)

dim cell
Cell = sheet.getCellrangeByName("A1")
Tables.insertNewByName(pivotSheetNameOverview, Cell.CellAddress, Descriptor)

'dim names

dim hasElem

wait 2000

Dim table
table=Sheets.getByName("Ueberblick ...
edit retag flag offensive close merge delete


-1- Posting wiki is not a good idea. Any contributor with enough "karma" can edit questions and answers by others anyway.- and one with low karma will not be able to help you.
-2- You surely studied chapter "15.5.6. Data pilot and pivot tables" of Andrew Pitonyak's famous OOME book. Very few contrbutors will be able to help you beyond that.
-3- You may also post (properly cross-linked) in the much better structurd
There are some clever guys trained in old times ...

Lupp gravatar imageLupp ( 2017-07-25 11:29:36 +0100 )edit

Yes - I use Andrew Pitonyak's book. I'll try the forum. There are several examples regarding pivot tables in this forum. Perhaps somebody can help but I fear this is a question only a LO developer can answer. Using API in LO is always kind of exploring an unknown jungle.

dumischbaenger gravatar imagedumischbaenger ( 2017-07-27 07:26:33 +0100 )edit

Just a remark: The is not owned by or under control of the Apache Software Foundation. The mentioned forum also accepts questions and contributions concerning mainly LibreOffice. Some of the most experienced contributors there regard AOO near the end of its life.
Feel invited to also use that forum as one for LibO, a structured one as distinguished from this breathlessly running ticker.

Lupp gravatar imageLupp ( 2017-07-27 08:56:32 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-11-04 18:06:14 +0100

JPAlpano gravatar image

updated 2017-11-05 03:12:35 +0100

The Virtual "Data" field you are referring to can be accessed by using the name "Data".

oVirtualDataField = oYourTableDescr.getDataPilotFields().getByName("Data")

You can then move its orientation using the normal way.

oVirtualDataField.Orientation =

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-07-25 08:43:54 +0100

Seen: 477 times

Last updated: Nov 05 '17