Pivot Table Macro

Keyboard macros or custom scripts

Pivot Table Macro

Postby bimbernzg » Thu Oct 08, 2020 1:36 am

Hi

I'm new to the basic macros in Calc. I need to make pivot table from data on sheet. I've got code from this forum and change it a little bit. This is what I've got so far

Code: Select all   Expand viewCollapse view
Sub DataPilot
oController = ThisComponent.CurrentController
oSheetObj = oController.ActiveSheet
DataCellRange = oSheetObj.getCellRangeByName("A1:C10")
RangeAddress = DataCellRange.RangeAddress
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("NewDataPilot") THEN
   Tables.removeByName("NewDataPilot")
End If

Descriptor = Tables.createDataPilotDescriptor()      'Descriptor contains the description of a DataPilot Table
Descriptor.ShowFilterButton = False                   'Don't show the Filter Button
Descriptor.setSourceRange(RangeAddress)     'RangeAddress is defined above to cover A1:C10       
Fields = Descriptor.getDataPilotFields           


'Set the Enum DataPilotFieldOrientation from com.sun.star.sheet.DataPilotField
Field1 = Fields.getByIndex(0)
Field1.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW                                   

Field2 = Fields.getByIndex(1)
Field2.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW 
Field3 = Fields.getByIndex(2)
Field3.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
Field3.Function = com.sun.star.sheet.GeneralFunction.SUM
Descriptor.RowGrand = "FALSE"   'Turn off the Total line of the Table
Cell = oSheetObj.getCellrangeByName("A15")
Tables.insertNewByName("NewDataPilot", Cell.CellAddress, Descriptor)
End sub


The result is on this image

Image

However I need to make this Pivot Table exactly like this

Image

What I need to change and how can I move final table to the select sheet? Please help :)
OpenOffice 4.1.7 Windows 10
bimbernzg
 
Posts: 2
Joined: Wed Oct 07, 2020 11:38 pm

Re: Pivot Table Macro

Postby FJCC » Thu Oct 08, 2020 4:26 am

Try this. It is hard coded for exactly the data range in your example. Is that good enough for your needs?
Code: Select all   Expand viewCollapse view
Sub DataPilot
oController = ThisComponent.CurrentController
oSheetObj = oController.ActiveSheet
DataCellRange = oSheetObj.getCellRangeByName("A1:C5")
RangeAddress = DataCellRange.RangeAddress
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("NewDataPilot") THEN
   Tables.removeByName("NewDataPilot")
End If

Descriptor = Tables.createDataPilotDescriptor()      'Descriptor contains the description of a DataPilot Table
Descriptor.ShowFilterButton = False                   'Don't show the Filter Button
Descriptor.setSourceRange(RangeAddress)     'RangeAddress is defined above to cover A1:C10       
Fields = Descriptor.getDataPilotFields           


'Set the Enum DataPilotFieldOrientation from com.sun.star.sheet.DataPilotField
Field1 = Fields.getByIndex(0)
Field1.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW                                   

Field2 = Fields.getByIndex(1)
Field2.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
Field3 = Fields.getByIndex(2)
Field3.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
Field3.Function = com.sun.star.sheet.GeneralFunction.SUM
Descriptor.RowGrand = TRUE   'Turn off the Total line of the Table
Cell = oSheetObj.getCellrangeByName("A9")
Tables.insertNewByName("NewDataPilot", Cell.CellAddress, Descriptor)

oCellRng = oSheetObj.getCellrangeByName("A9:C9")
oCellRng.clearContents(1023)
End sub
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 8257
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Pivot Table Macro

Postby bimbernzg » Thu Oct 08, 2020 9:18 pm

Wow thanks for quick answer. Actually it gaves me idea of what I should fix there. First of all my basic table will always have 3 columns but unknown number of rows so I simply add C999999 to the range and delete "empty" row in pivot table (it's not elegant but I don't know there is some code to detect how much rows will be in first table). But can I move the pivot table to the new sheet? there is comand for that?
OpenOffice 4.1.7 Windows 10
bimbernzg
 
Posts: 2
Joined: Wed Oct 07, 2020 11:38 pm

Re: Pivot Table Macro

Postby Villeroy » Thu Oct 08, 2020 9:43 pm

If you would insert new rows for new data, the pivot range would adjust automatically together with any formulas, named ranges, conditional formattings etc. If you would store your data in a database you could link a pivot to the database data without the slightest doubt about the row count.
Insertion of rows adjusts all references in all types of formulas and ranges. Just turn on Toools>Options>Calc>General... "Expand references ...". With this option being turned on, ranges do expand even when you insert directly below (or right of) the referenced range.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30165
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pivot Table Macro

Postby Villeroy » Thu Oct 08, 2020 10:21 pm

Anyway, macro coders hate spreadsheets.

This is a macro to add new rows to the current region of used cells taking care of formulas and "expand references" option: viewtopic.php?f=21&t=2350

This is a function to get the current region around a given cell:
Code: Select all   Expand viewCollapse view
Function getCurrentRegion(oRange)
Dim oCursor
   oCursor = oRange.getSpreadSheet.createCursorByRange(oRange)
   oCursor.collapseToCurrentRegion
   getCurrentRegion = oCursor
End Function

The CellRangeAddress of the returned range can be used to adjust the pivot's source area without rebuilding each and every property from scratch.
Something like this changes the source range only:
Code: Select all   Expand viewCollapse view
objSheet = ThisComponent.Sheets.getByIndex(0)
pv = objSheet.DataPilotTables.getByIndex(0)
rg = getCurrentRegion(objSheet.getCellByPosition(0,0))
pv.setSourceRange(rg.getRangeAddress()
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30165
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests