Pivot Table Macro

[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

[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

[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?

[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.

[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()