[Solved] Create DataPilot with OpenOffice Basic (Macro)

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
FumiC1980
Posts: 9
Joined: Tue Dec 23, 2008 9:17 pm

[Solved] Create DataPilot with OpenOffice Basic (Macro)

Post by 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
Last edited by FumiC1980 on Fri Dec 26, 2008 2:16 pm, edited 1 time in total.
OOo 3.0.X on Ubuntu 8.x
User avatar
Villeroy
Volunteer
Posts: 31292
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Create DataPilot with OpenOffice Basic (Macro)

Post by 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()
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
FumiC1980
Posts: 9
Joined: Tue Dec 23, 2008 9:17 pm

Re: Create DataPilot with OpenOffice Basic (Macro)

Post by FumiC1980 »

This worked fine!

8-)

THX
OOo 3.0.X on Ubuntu 8.x
windmarc
Posts: 14
Joined: Sat Jul 03, 2010 9:34 pm

Re: Create DataPilot with OpenOffice Basic (Macro)

Post by 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.
OpenOffice 3.3.0 on Windows 7
FJCC
Moderator
Posts: 9317
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Create DataPilot with OpenOffice Basic (Macro)

Post by 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
Attachments
DataPilot.ods
(11.29 KiB) Downloaded 714 times
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.
windmarc
Posts: 14
Joined: Sat Jul 03, 2010 9:34 pm

Re: [Solved] Create DataPilot with OpenOffice Basic (Macro)

Post by windmarc »

Thanks a lot for the help. I will work on it.
OpenOffice 3.3.0 on Windows 7
windmarc
Posts: 14
Joined: Sat Jul 03, 2010 9:34 pm

Re: [Solved] Create DataPilot with OpenOffice Basic (Macro)

Post by windmarc »

Works great for me!
Many thanks for a simple solution to my problem.
OpenOffice 3.3.0 on Windows 7
Post Reply