[Solved] VB Automation Difficulty

Java, C++, C#, Delphi... - Using the UNO bridges
Post Reply
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

[Solved] VB Automation Difficulty

Post by Charlie Young »

Last November I had an exchange with pu8y about Calc automation using VB6. See changing the colors of series in a bar chart using VB6. The problem was apparently fixed in VB6, but as an academic exercise I have been trying to get the trick to work first in VB 2008, then in 2010. The following is mostly a straightforward adaptation of pu8y's code.

Code: Select all

Module Module1
    Public oSM As Object    'Root object for accessing OpenOffice from VB
    Public oDesk As Object  'First objects from the API
    Public arg(1)
    Sub Main()
        'Instantiate OOo : this line is mandatory with VB for OOo API
        oSM = CreateObject("com.sun.star.ServiceManager")
        'Create the first and most important service
        oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
        LoadChartDoc()
    End Sub
    Sub LoadChartDoc()
        Dim oDoc As Object
        Dim Charts As Object
        Dim chart As Object
        Dim rect As Object
        Dim rangeAddress(0) As Object
        Dim oSheet As Object
        Dim oDiagram As Object
        Dim oCoords As Object
        Dim oCoord As Object
        Dim oChartTypes As Object
        Dim oChartType As Object
        Dim oSeries As Object

        arg(0) = MakePropertyValue("Hidden", False)
        arg(1) = MakePropertyValue("MacroExecutionMode", 4)

        'Open an existing doc (pay attention to the syntax for first argument)

        oDoc = oDesk.loadComponentFromURL("file:///C:/......./StackedBarColors.ods", "_blank", 0, arg)
        oSheet = oDoc.getSheets().getByIndex(0)
        rect = oSM.Bridge_GetStruct("com.sun.star.awt.Rectangle")
        rangeAddress(0) = oSM.Bridge_GetStruct("com.sun.star.table.CellRangeAddress")
        Charts = oSheet.getCharts()
        rect.x = 2200
        rect.Y = 4000
        rect.Width = 35000
        rect.Height = 15000
        rangeAddress(0).Sheet = 0
        rangeAddress(0).StartColumn = 1
        rangeAddress(0).StartRow = 3
        rangeAddress(0).EndColumn = 13
        rangeAddress(0).EndRow = 5

        If Charts.HasByName("LineChart") Then
            Charts.RemoveByName("LineChart")
        End If

        Call Charts.addNewByName("LineChart", rect, rangeAddress, True, True)
        chart = Charts.getByName("LineChart").embeddedObject

        'set as stackable bar
        chart.Diagram = oSM.createInstance("com.sun.star.chart.StackableDiagram")
        chart.Diagram.Stacked = True
        chart.Diagram.Percent = False
        'set data series in rows
        chart.Diagram.DataRowSource = 0
        oDiagram = chart.getFirstDiagram()
        oCoords = oDiagram.getCoordinateSystems()
        oCoord = oCoords(0)

        oChartTypes = oCoord.getChartTypes()
        oChartType = oChartTypes(0)

        oSeries = oChartType.getDataSeries()
        oSeries(0).Color = RGB(0, 128, 0)
        oSeries(1).Color = RGB(0, 0, 255)

    End Sub

    Function MakePropertyValue(ByVal cName, ByVal uValue) As Object

        Dim oPropertyValue As Object

        oPropertyValue = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
        oPropertyValue.Name = cName
        oPropertyValue.Value = uValue

        MakePropertyValue = oPropertyValue

    End Function


The program runs, loads the spreadsheet, creates the chart and inserts it, and changes the chart to stacked, but when it tries to access the dataseries, it gets to the line

Code: Select all

oCoords = oDiagram.getCoordinateSystems()
and bombs with the error:

System.Runtime.InteropServices.SafeArrayTypeMismatchException was unhandled

Specified array was not of the expected type

I have searched and searched, and though there are many links to discussions of that particular error, I haven't found a solution that works in this case.

I did do the trick of adding references to the cli dlls from the SDK (which may also be extracted from the OOo installation CAB file), which allows variables to be declared as UNO objects. When trying that, I've just managed to get myself tied up in knots. For instance, if I declare oDiagram thus

Code: Select all

Dim oDiagram As unoidl.com.sun.star.chart2.XDiagram
I am told

Error 1 'getCoordinateSystems' is not a member of 'unoidl.com.sun.star.chart2.XDiagram'.

Which is true: getCoordinateSystems is a member of XCoordinateSystemContainer. However, the FirstDiagram (an XDiagram), is supposed to have an XCoordinateSystemContainer interface. There seem to be some casting and interface query problems here which I have not been able to overcome, though I have repeatedly tried.

I have though, using the SDK, been able to translate this into c++ where it does work. After the song and dance of connecting to the office and loading the spreadsheet, I do (oDoc is the XComponent returned by loadComponentFromURL).

Code: Select all

//query for a XSpreadsheetDocument interface 
	Reference< XSpreadsheetDocument > oSheetDoc (oDoc, UNO_QUERY); 
 
//use it to get the XSpreadsheets interface 
	Reference< XSpreadsheets > oSheets = oSheetDoc->getSheets(); 
 
//use getByName to get a reference (type Any) 
	Any oSheet = oSheets->getByName( OUString::createFromAscii("Sheet1"));
	
	Reference< XSpreadsheet > oSpSheet (oSheet, UNO_QUERY);
	
	Sequence<CellRangeAddress> rangeAddress(1);
	rangeAddress[0].Sheet = 0;
    rangeAddress[0].StartColumn = 1;
    rangeAddress[0].StartRow = 3;
    rangeAddress[0].EndColumn = 13;
    rangeAddress[0].EndRow = 5; 

	com::sun::star::awt::Rectangle rect;
	rect.X = 2200;
    rect.Y = 4000;
    rect.Width = 35000;
    rect.Height = 15000;

	Reference<XTableChartsSupplier> chartsheet(oSheet, UNO_QUERY);
	OUString chartName = OUString::createFromAscii("LineChart");
	Reference<XTableCharts> oChartCollection = chartsheet->getCharts();
	
	if (oChartCollection->hasByName(chartName))
		oChartCollection->removeByName(chartName);
	
	oChartCollection->addNewByName(chartName, rect, rangeAddress, 1, 1);
		
	Reference<XTableChart> oTableChart(oChartCollection->getByName(chartName), UNO_QUERY);
	Reference<XEmbeddedObjectSupplier> xEmbeddedObjectSupplier(oTableChart, UNO_QUERY);
	Reference<XInterface> chart = xEmbeddedObjectSupplier->getEmbeddedObject();
	Reference<com::sun::star::chart::XChartDocument> xChartDoc(chart, UNO_QUERY);
	
	// Change the chart type to the specified type
	Reference<XMultiServiceFactory> xMultiServiceFactory(xChartDoc, UNO_QUERY);
	OUString chartType;
	chartType = OUString::createFromAscii("com.sun.star.chart.BarDiagram");
	
	Reference<XInterface> newChart = xMultiServiceFactory->createInstance(chartType);
	Reference<com::sun::star::chart::XDiagram> xDiagram(newChart, UNO_QUERY);
		
	xChartDoc->setDiagram(xDiagram);
	
	Reference< XPropertySet > xPropertySet(xDiagram, UNO_QUERY);
	xPropertySet->setPropertyValue(OUString::createFromAscii("DataRowSource"), (Any) (sal_Int8) 0);
	xPropertySet->setPropertyValue(OUString::createFromAscii("Stacked"),(Any) sal_True);
	xPropertySet->setPropertyValue(OUString::createFromAscii("Percent"),(Any) sal_False);	
	
	Reference< com::sun::star::chart2::XChartDocument > xChartDocument2(chart, UNO_QUERY);
	Reference< com::sun::star::chart2::XDiagram > xFirstDiagram = xChartDocument2->getFirstDiagram();

	Reference< XCoordinateSystemContainer > xCoordinateSystemContainer(xFirstDiagram, UNO_QUERY);
	Sequence< Reference<XCoordinateSystem> > xCoordinateSystems = xCoordinateSystemContainer->getCoordinateSystems();	
		
	Reference<XCoordinateSystem> xCoordinateSystem = xCoordinateSystems[0];
		
	Reference< XChartTypeContainer > xChartTypeContainer(xCoordinateSystem, UNO_QUERY);
	Sequence< Reference<XChartType> > xChartTypes = xChartTypeContainer->getChartTypes();
	Reference<XChartType> xChartType = xChartTypes[0];

	Reference< XDataSeriesContainer > xDataSeriesContainer(xChartType, UNO_QUERY);
	Sequence< Reference<XDataSeries> > xDataSeries = xDataSeriesContainer->getDataSeries();
	
	long nSeries = rangeAddress[0].EndRow - rangeAddress[0].StartRow;
	Sequence<Reference<XDataSeries>> xSeries(nSeries);
	Sequence<Reference< XPropertySet >> xSeriesPropertySet(nSeries);
	
	long i;
	for(i = 0;i < nSeries; i++)
	{
		xSeries[i] = Reference<XDataSeries>(xDataSeries[i],UNO_QUERY);
		xSeriesPropertySet[i] = Reference<XPropertySet>(xSeries[i],UNO_QUERY);
	}
	
	xSeriesPropertySet[0]->setPropertyValue(OUString::createFromAscii("Color"),(Any) 0x00ff00L);
	xSeriesPropertySet[1]->setPropertyValue(OUString::createFromAscii("Color"),(Any) 0x0000ffL );
	
For anyone wishing to play with this, StackedBarColors.ods is available in the original thread linked at the top of this post (It also contains OOo Basic macros for the tasks). I thought I could solve this myself eventually, but hopefully someone can teach me something about VB here, even though I would generally prefer working with c++. It is true that the VB syntax for this is a lot simpler, when it works.
Last edited by Charlie Young on Tue May 08, 2012 6:47 pm, edited 2 times in total.
Apache OpenOffice 4.1.1
Windows XP
B Marcelly
Volunteer
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: VB Automation Difficulty

Post by B Marcelly »

Hi,
Charlie Young wrote:and bombs with the error:
System.Runtime.InteropServices.SafeArrayTypeMismatchException was unhandled
Specified array was not of the expected type
The probable cause is this regression, to be corrected in OOo 3.4 :
Bug 117010 - oo 3.3 oleautobridge.uno.dll is buggy
All methods which return a sequence fail with OOo 3.3.0 used through COM Automation.
Bernard

OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: VB Automation Difficulty

Post by Charlie Young »

B Marcelly wrote:Hi,
Charlie Young wrote:and bombs with the error:
System.Runtime.InteropServices.SafeArrayTypeMismatchException was unhandled
Specified array was not of the expected type
The probable cause is this regression, to be corrected in OOo 3.4 :
Bug 117010 - oo 3.3 oleautobridge.uno.dll is buggy
All methods which return a sequence fail with OOo 3.3.0 used through COM Automation.
First I unwisely downloaded oleautobridge.uno.dll from a third-party site, and trying to use it (with 3.3) crashed badly. I restored the 3.3 .dll and got back to where I was. I just downloaded the 3.4 alpha release, and though the oleautobridge.dll seems to be a different version, using it hasn't fixed the problem yet. It isn't worth my while to try to dig up the 3.2 version, so I'll just wait and see how this develops. I do see where this problem has made it into consideration for 3.3.1, which I'm sure we'll all be seeing before 3.4 gets finalized.

I guess I won't mark this as solved. I don't think identifying the problem necessarily equates to a solution. :?
Apache OpenOffice 4.1.1
Windows XP
B Marcelly
Volunteer
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: VB Automation Difficulty

Post by B Marcelly »

Hi,
Charlie Young wrote:I guess I won't mark this as solved. I don't think identifying the problem necessarily equates to a solution. :?
Then tag the first message with [Issue] and appropriate icon.
Although this Issue has status "fixed", it still has to be checked and integrated in a developer snapshot for 3.4.

And 3.3.1 is not yet decided, despite numerous regressions. :evil:
Bernard

OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: [Solved] VB Automation Difficulty

Post by Charlie Young »

This is apparently fixed in 3.4. Very nice!
Apache OpenOffice 4.1.1
Windows XP
Post Reply