Page 1 of 1
Pivot Table Macro
Posted: Thu Oct 08, 2020 1:36 am
by bimbernzg
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
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
However I need to make this Pivot Table exactly like this
What I need to change and how can I move final table to the select sheet? Please help
Re: Pivot Table Macro
Posted: Thu Oct 08, 2020 4:26 am
by FJCC
Try this. It is hard coded for exactly the data range in your example. Is that good enough for your needs?
Code: Select all
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
Re: Pivot Table Macro
Posted: Thu Oct 08, 2020 9:18 pm
by bimbernzg
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?
Re: Pivot Table Macro
Posted: Thu Oct 08, 2020 9:43 pm
by Villeroy
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.
Re: Pivot Table Macro
Posted: Thu Oct 08, 2020 10:21 pm
by Villeroy
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
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
objSheet = ThisComponent.Sheets.getByIndex(0)
pv = objSheet.DataPilotTables.getByIndex(0)
rg = getCurrentRegion(objSheet.getCellByPosition(0,0))
pv.setSourceRange(rg.getRangeAddress()