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
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
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
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
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?
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
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:
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