[Solved] Create DataPilot with OpenOffice Basic (Macro)

[FumiC1980]

Hello! : )

Is there a way to create a DataPilot table with Macros?

If I record a macro with Calc (no selection done),
this will be the basic source code:

Code: Select all

sub pivot
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataDataPilotRun", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DataPilotExec", "", 0, Array())


end sub

I am very new to Basic programming, so I need a little help with this...

Sincerly,
Christian

[Villeroy]

[Moved from Calc to API forum]

http://wiki.services.openoffice.org/wik ... ilot_Table
The entry point is:

Code: Select all

oDescriptor = oSheetObj.DatapilotTables.createDataPilotDescriptor()

[FumiC1980]

This worked fine!



THX

[windmarc]

I have the same question and tried the solution without success. I've also read a few similar questions on this board with generic solutions that I wasn't able to use. I am familiar with Calc and BASIC, but a beginner regarding OpenOffice macros, although I have been able to write/record simple ones, or adapt some of the ones published on this site. I hope someone can help me with something concrete.
When I try to record a macro to create a DataPilot table, this is what I get:

Code: Select all

sub Pivot

rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object

rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1:$C$30"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataDataPilotRun", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DataPilotExec", "", 0, Array())

end sub

It doesn't work, and I understand that the macro recorder has limited functionality.
Regarding the solution in this post, I may need some help about how to apply it. I added the line:

Code: Select all

oDescriptor = oSheetObj.DatapilotTables.createDataPilotDescriptor()

at the beginning of the script.
So, my macro now looks like this:

Code: Select all

sub Pivot
rem -------------------------
rem Added following line per http://user.services.openoffice.org/en/forum/viewtopic.php?f=20&t=13598
oDescriptor = oSheetObj.DatapilotTables.createDataPilotDescriptor()

rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object


rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1:$C$30"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataDataPilotRun", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DataPilotExec", "", 0, Array())

end sub

It still doesn work. I get the following error: "BASIC runtime error. Object variable not set."
I may be missing something simple.

I also tried the example from http://wiki.services.openoffice.org/wik ... ilot_Table
This script is apparently not based on BASIC syntax, and I was not able to run it . The // for comments, and semi-colons at the end of the lines were rejected, and it was choking on a line like:

Code: Select all

com.sun.star.sheet.XDataPilotTablesSupplier xDPSupp = (com.sun.star.sheet.XDataPilotTablesSupplier)

I would appreciate any help. Many thanks.

[FJCC]

Creating a DataPilot table with a macro requires several steps. I have attached a file that uses the following code to insert a datapilot table on Sheet1. I hope the comments provide some guidance in understanding the code.

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				
Field1 = Fields.getByIndex(0)   'The first column of the data range has index 0

'Set the Enum DataPilotFieldOrientation from com.sun.star.sheet.DataPilotField
Field1.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW												

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

[windmarc]

Thanks a lot for the help. I will work on it.

[windmarc]

Works great for me!
Many thanks for a simple solution to my problem.