Page 1 of 2

[Solved] Call OOo API using MS Com Automation interface

Posted: Fri Oct 10, 2008 5:39 am
by dnelson
I have a simple COBOL program (below) that calls Microsoft excel to create an excel spreadsheet with the word "test" in the first cell. I've read the automation bridge documentation and I am still confused about how to convert this program to use the open office calc api. Any help would be very much appreciated.

$set sourceformat(variable)
$set ooctrl(+P)
IDENTIFICATION DIVISION.
ENVIRONMENT DIVISION.

class-control.
MSExcel is class "$OLE$Excel.Application".

DATA DIVISION.
WORKING-STORAGE SECTION.

01 ExcelObject object reference.
01 WorkBooksCollection object reference.
01 WorkBook object reference.
01 Cell object reference.

PROCEDURE DIVISION.

*> Create a new instance of Microsoft Excel
invoke MSExcel "new" returning ExcelObject

*> Make Excel visible
invoke ExcelObject "setVisible" using by value 1

*> Get the collection of WorkBooks
invoke ExcelObject "getWorkBooks" returning WorkBooksCollection

*> Add a new WorkBook to the collection
invoke WorkBooksCollection "Add" returning WorkBook

invoke ExcelObject "getCells" using by value 1 by value 1 returning Cell
invoke Cell "setValue" using by reference z"test"
invoke Cell "finalize" returning Cell

invoke WorkBook "SaveAs" using by reference z"C:\TEMP\EXCLTEST.XLS"

*> Close the WorkBook.
invoke WorkBook "Close" using by value 0

*> Finalize all objects
invoke WorkBook "finalize" returning WorkBook
invoke WorkBooksCollection "finalize" returning WorkBooksCollection

*> Exit Excel
invoke ExcelObject "Quit"
invoke ExcelObject "Finalize" returning ExcelObject

GOBACK.

Re: How to call OO API using Microsoft Com Automation interface

Posted: Fri Oct 10, 2008 9:09 am
by Villeroy
Programming OpenOffice.org with Visual Basic
Without any office installed you could experiment with the free ODF standard. Just get a skeleton of xml files from any spreadsheet, modify content.xml and put it in a zip with suffix .ods.

Re: How to call OO API using Microsoft Com Automation interface

Posted: Fri Oct 10, 2008 3:24 pm
by dnelson
I'm sorry Villeroy. I want to be able to maintain the same programming style with as few alterations as possible. I don't see how your suggestion enables me to do quick conversions. Ideally, I simply want to be able to replace the name of the Microsoft class and methods with the name of the Open Office class and methods where needed.

Re: How to call OO API using Microsoft Com Automation interface

Posted: Fri Oct 10, 2008 3:39 pm
by Villeroy
You've got to stay with Excel.

SORRY! You may try the "VBA compatibility API" as described in http://wiki.services.openoffice.org/wik ... _languages
For ranges on sheets with values, formulas and some basic formatting it may fit your needs. Don't expect too much, however.

[sent PM to dnelson]

Re: How to call OO API using Microsoft Com Automation interface

Posted: Sun Oct 19, 2008 1:12 pm
by ms777
Hi,

I believe the original question was more targeting at how to invoke OO generally through ´the COM interface. Please see http://www.oooforum.org/forum/viewtopic ... 560#299560 as a Visual Basic Example.

I speculate that the following replacements may work:

MSExcel is class "$OLE$Excel.Application"
->
OServManager is class "$OLE$com.sun.star.ServiceManager"

invoke MSExcel "new" returning ExcelObject
->
invoke OServManager "createInstance" returning StarDesktop
invoke StarDesktop "loadComponentFromURL" using by value "private:factory/scalc" using by value "_blank" using by value 0 using (no idea how to generate empty array in COBOL) returning CalcDoc

Good luck,

ms777

Re: How to call OO API using Microsoft Com Automation interface

Posted: Sun Oct 19, 2008 1:24 pm
by dnelson
Thanks a lot! It may be a day or two before I have an opportunity to try that, but I'll let you know.

Re: How to call OO API using Microsoft Com Automation interface

Posted: Mon Oct 20, 2008 10:32 pm
by dnelson
Thanks for your help/suggestions. I've tried dozens of variations of what you have suggested and have looked at the link (among many others). Basically, I can only get the first line of code to work:

OServManager is class "$OLE$com.sun.star.ServiceManager"
invoke OServManager "new" returning StarDesktop

After that, nothing works. Any variation of the following always returns some kind of error (which follows).

invoke StarDesktop "loadComponentFromURL" using by reference "private:factory/scalc"
invoke StarDesktop "loadComponentFromURL" using by reference "C:\temp\test.ods"
by reference "_blank"
by value 0
by value 0
returning CalcDoc

Exception 65540 not trapped by the class oleexceptionmanager.
Description: "OLE Name not found"
(80020006): Unknown name.

I'm about out of options (or persistence). It would appear that Microsoft wins again.

Re: How to call OO API using Microsoft Com Automation interface

Posted: Mon Oct 20, 2008 10:51 pm
by Villeroy
invoke StarDesktop "loadComponentFromURL" using by reference "C:\temp\test.ods"
Use an URL like a browser: "file:///C:/temp/test.ods"

Re: How to call OO API using Microsoft Com Automation interface

Posted: Mon Oct 20, 2008 11:00 pm
by dnelson
I tried that and several variations to no avail. It appears from the error message (OLE Name not found) that it is not even getting far enough to consider the file name.

Re: How to call OO API using Microsoft Com Automation interface

Posted: Tue Oct 21, 2008 1:31 am
by Villeroy
OK, what are you trying to do? For simple spreadsheets you may consider to generate them from scratch. ODF is a free file format that wraps xml into zip-archives. You can generate valid office files without having any office software installed. Create a template with all the required formatting but no content. Fill in some example records and watch content.xml.
If your source data happen to be xml, you can use xslt. Recent thread with simple import filter: [Solved] Is there a way to open XML file in Calc?

Re: How to call OO API using Microsoft Com Automation interface

Posted: Tue Oct 21, 2008 4:07 am
by dnelson
Alright, since you asked, here's the long of it. We have been writing reports for several years that are quite extensive. These reports are produced by Micro Focus Net Express COBOL programs that read SQL Server database tables and build data into Microsoft Excel spreadsheets. The programs use a lot of different methods to build these spreadsheets. Some of the reports are quite involved, some are simple. Some are created on the fly as users pick and choose the columns they want. There are totals, different colors assigned, some rows and columns are hidden, some columns are plain text, others are numbers with formatting (two decimal places), some are numbers without formatting, and dates, some are bold, etc (you get the picture). I have no complaints about the ease with which these reports can be produced using the COM interface for Excel. In a sense, we just use it right out of the box and it works. Here are some examples of the types of statements that are being used in these programs:

invoke MSExcel "new" returning ExcelObject
invoke ExcelObject "setVisible" using by value 1
invoke ExcelObject "getWorkBooks" returning WorkBooksCollection
invoke WorkBooksCollection "Add" returning WorkBook
invoke ExcelObject "getWorkSheets" returning WorkSheetsCollection
invoke WorkSheetsCollection "Add" returning WorkSheet
invoke WorkSheet "setName" using z"Company 01"
invoke WorkSheetsCollection "getItem" using by value 1 returning WorkSheet
invoke WorkSheet "activate"
invoke ExcelObject "getColumns" using z"A:M"
invoke getColumns "Select"
invoke getColumns "setNumberFormat" using by reference "@"
invoke getColumns "setNumberFormat" using by reference "0"
invoke getColumns "setNumberFormat" using by reference "$0.00"
invoke getColumns "finalize" returning getColumns
invoke getColumns "AutoFit"
invoke getColumns "getEntireColumn" returning getEntireColumn
invoke getEntireColumn "setHidden" using by value 1
invoke getColumns "finalize" returning getColumns
invoke ExcelObject "getCells" using by value 1 by value 1 returning Cell
invoke Cell "setValue" using by reference WORK-FIELD
invoke Cell "finalize" returning Cell
invoke ExcelObject "getRange" using z"A1:U2"returning CellRange
invoke CellRange "Select"
invoke CellRange "getFont" returning Font
invoke Font "setName" using z"Comic Sans MS"
invoke Font "setSize" using by value 36 size 4
invoke font "setBold" using by value 1 size 4
invoke font "setColorIndex" using by value 45 *> orange
invoke CellRange "finalize" returning CellRange
invoke ExcelObject "getRows" using WORK-FIELD returning getRows
invoke getEntireRow "setHidden" using by value 1
invoke getRows "finalize" returning getRows
invoke WorkBook "SaveAs" using by reference filename by value FileFormat by reference z"password"
invoke WorkBook "Close" using by value 0
invoke WorkBook "finalize" returning WorkBook
invoke WorkBooksCollection "finalize" returning WorkBooksCollection
invoke ExcelObject "Quit"
invoke ExcelObject "Finalize" returning ExcelObject

Now, maybe I should have just presented all of this to begin with. My hope was that if I could just convert some of the simplest of our programs to use OO Calc instead of MS Excel, then I would be on my way. I don't even know if all of the calls that are available in Excel are available in Calc. Maybe this is futile. I was hoping some OO experts would either tell me that this can be done because OO is so good, or that there is no way, so just give up trying. All of the OO documentation seems to suggest that a lot of effort has been poured into OO to make it a viable alternative for MS Office. I don't particularly look forward to converting all of these applications from MS Office to OO, but the pressure from management to save money is great. We need to know if OO is really viable. When you are talking about dozens and potentially hundreds of employees being able to live without MS Office, then obviously that is a lot of money. I figured if I could get it to work for our Excel applications then I could also convert our MS Word applications to OO Writer.

I'm not really intending to become a C, Java or VB programmer. COBOL has worked quite well, but integrating it with a lot of "free stuff" is new, although we have incorporated quite a bit of Perl into it.

So, am I barking up the wrong tree? OO 2.4 is my first experience with OO and I if we can't get any of this stuff to work there is no sense in even looking at OO 3.0. So, to answer your question more directly, these are not simple spreadsheets. These are robust reporting and letter generation mechanisms. I've downloaded and studied the Open Office.org 2.3 Developer's Guide (1250 pages!) and I still have a lot of confusion.

Re: How to call OO API using Microsoft Com Automation interface

Posted: Tue Oct 21, 2008 9:55 am
by Villeroy
You don't need to write programs to create a new document and format by means of hard-coded property values. Just load a prepared template.
...programs that read SQL Server database tables and build data into Microsoft Excel spreadsheets ...
Where is that part? You don't need a program neither. Calc (and Excel as well) provide tools to bind cell ranges to database recordsets.

Re: How to call OO API using Microsoft Com Automation interface

Posted: Tue Oct 21, 2008 4:02 pm
by dnelson
Forgive me if I sound short. What part of "Some are created on the fly as users pick and choose the columns they want." do you not understand? Most of these reports are customized by the users who choose options from a screen that serves as an interface. Your statement, "Just load a prepared template" tells me that you are trying to over-simplify this. The beauty and flexibility of what has already been created lies in the fact that it is not tied to prepared templates. Again, my original intent was to see if we can do using OO what was easily done using MS Office. It sounds like the answer is no - not that it can't be done, but that it can't be done easily. This is a classic case of spending more money in time and resources trying to get free stuff to work than would be saved if we just used the purchased software.

Re: How to call OO API using Microsoft Com Automation interface

Posted: Tue Oct 21, 2008 5:15 pm
by Villeroy
10/Oct: Subject: How to call OO API using Microsoft Com Automation interface
Answered with a link Programming OpenOffice.org with Visual Basic

Then you revealed that you do not want to access the OOo Api. You want to access the Excel API in OOo.
Ideally, I simply want to be able to replace the name of the Microsoft class and methods with the name of the Open Office class and methods where needed.
First I thought this would be impossible, then I found the Excel-compatibility-API which may even do that trick to some extend.

Then you obviously decided to use the OOo-API anyway but don't get the translation from the VB examples to your preferred language.
OServManager is class "$OLE$com.sun.star.ServiceManager"
invoke OServManager "new" returning StarDesktop

After that, nothing works. Any variation of the following always returns some kind of error (which follows).
At this point you are completely on your own, unless you find somebody who managed to access this API using Cobol via COM.
Some are created on the fly as users pick and choose the columns they want.
Admittedly, I missed that part.

It looks as if you really have to stay with Excel -- forever, since I would not hold my breath waiting for an Excel clone before 2020 (seminar on historic APIs and data archeology).

Re: How to call OO API using Microsoft Com Automation interface

Posted: Tue Oct 21, 2008 6:16 pm
by dnelson
Sorry for the confusion. Apparently I haven't stated my intentions very well. In all seriousness, I thought that Calc was supposed to be an Excel clone. Am I mistaken about that? It is advertised as being 100% Office Suite Compatible. Even the developer's guide states that the API is supposed to offer the same type of control over the product that is comparable to the MS Office API. I still suspect that it will work (using COBOL), but that you are correct in saying that I am on my own. And yes, you are correct in that I "don't get the translation from the VB examples to your preferred language". I find it interesting though that most of the development I did using COBOL to access the MS COM API was done by translating VB examples (both Excel and Word). I do appreciate your efforts in trying to assist me.

Re: How to call OO API using Microsoft Com Automation interface

Posted: Tue Oct 21, 2008 11:21 pm
by Villeroy
This office suite is an application on it's own right since 1985. http://en.wikipedia.org/wiki/StarOffice
It offers to load other programs documents more or less well. I believe it is the second best option for Microsoft documents but it can't be perfect since it has been reverse engineered, many features are implemented differently, other features are missing in this office and a handful of features are missing in the other office.
It is advertised as being 100% Office Suite Compatible.
Where? I hope that this kind of hype is not spread from the openoffice.org domain nor any of the main contributors.
Even the developer's guide states that the API is supposed to offer the same type of control over the product that is comparable to the MS Office API.
The same type of control over a different product.

Re: How to call OO API using Microsoft Com Automation interface

Posted: Sat Jan 24, 2009 4:49 am
by dnelson
It's been a while since I posted here, but I have continued to work on this. I contacted Micro Focus, but didn't receive any help from them. They are biased towards Microsoft.

Here is a link that explains how it was done in FoxPro

http://www.tedroche.com/Present/VFPOOoAutomation.htm

I don't know FoxPro and can't seem to convert it. It basically says that in FoxPro, an array cannot be empty and I think the same is true in COBOL. I cannot figure out how to simulate/create an empty array in COBOL. I am also trying to create the array with one or more element, but can't figure that out either.

I can get a VB script example to work with an array with zero (empty) or more elements as it is easy to create an empty array in VB script.

I am uploading my test program (calctst4) in which I have experimented with all sorts of things to get the variantToAny error resolved. It may be of use to someone who looks at this problem. I realize this is a long shot, but maybe someone who has experimented with these types of things in COBOL can lend a hand.

No matter how I tried to upload the program as an attachment, I kept getting the error "The extension is not allowed.". I tried with an extension of .cbl, .txt, and no extension. So here is the program code. I realize not all of the code is necessary for the solution, but as I said, I was experimenting with a lot of things.

Code: Select all

      $set sourceformat(variable)
      $set ooctrl(+P)
       IDENTIFICATION DIVISION.
       ENVIRONMENT DIVISION.

       class-control.
           OServManager is class "$OLE$com.sun.star.ServiceManager"
           oleSafeArray is class "olesafea"  *> OLE SafeArray class
         chararray is class "chararry"     *> Character array class
           CharacterArray is class "chararry"
           olesup is class "olesup"
           OLEVariant is class "olevar"
            olebase is class "olebase"
           .

       DATA DIVISION.
        WORKING-STORAGE SECTION.

       copy mfole.cpy.

       copy olesafea.cpy.            *> SafeArray records

       01 theSafeArrayObj  object reference.

       01 saBound          SAFEARRAYBOUND occurs 1.

       01 ExcelObject          object reference.
       01 WorkBooksCollection  object reference.
       01 WorkBook             object reference.
       01 Cell                 object reference.

       01 ServManager          object reference.
       01 StarDesktop          object reference.
       01 CoreReflection       object reference.
       01 PropertyValue        object reference.
       01 ObjPropertyValue        object reference.
       01 aPropertyValue       pic x(40).
       01 CalcDoc              object reference.

       01 iIndex               pic x(4) comp-5 occurs 1.
       01 hIndex               pic x(4) comp-5.
       01 vIndex               pic x(4) comp-5.
       01 iValue               pic x(4) comp-5.
       01 iString              pic x(10).
       01 theCharArray     object reference.
       01 theStringLength      pic x(4) comp-5.
       01 theString            pic x(40).
       01 hResult              pic x(4) comp-5.
       01 theData              POINTER.
           01 aClass                 object reference.

       01 dimensions           pic x(4) comp-5.
       01 dimensionSize        pic x(4) comp-5.
      *01 intSafeArray         object reference.
       01 lBound               pic x(4) comp-5.
       01 uBound               pic x(4) comp-5.
      *01 hResult              pic x(4) comp-5.
       01 var-Type             pic x(4) comp-5.
       01 OLEBOOL-false        PIC s9(4) COMP-5 value 0.
       01 OLEBOOL-true         PIC s9(4) COMP-5 value -1.
       01 vData2               VARIANT.
      *01 v                VARIANT.
       01 theVariantObj    object reference.

           PROCEDURE DIVISION.

      *Set objServiceManager= WScript.CreateObject("com.sun.star.ServiceManager")
           display 'invoke OServManager "new" returning ServManager ' upon syserr
           invoke OServManager "new" returning ServManager
           display 'ServManager  "' ServManager  '"' upon syserr
           display ' ' upon syserr

      *Set objDesktop= objServiceManager.createInstance("com.sun.star.frame.Desktop")
           display 'invoke ServManager "createInstance" using "com.sun.star.frame.Desktop" returning StarDesktop' upon syserr
           invoke ServManager "createInstance" using "com.sun.star.frame.Desktop" returning StarDesktop
           display 'StarDesktop "' StarDesktop '"' upon syserr
           display ' ' upon syserr

*********************************** olesafearray
           display 'invoke olesafearray "new"....' upon syserr
           move 0 to llBound of saBound(1)
           move 1 to cElements of saBound(1)
           invoke olesafearray "new" using
               by value VT-VARIANT *> VT-SAFEARRAY *>VT-DISPATCH *> VT-ARRAY *> VT-VARIANT         *> Type:
               by value 1                  *> Single dimension
               by reference saBound(1)     *> Dimension boundaries
               returning theSafeArrayObj
           display 'theSafeArrayObj  "' theSafeArrayObj  '"' upon syserr
      *    stop "Press a key to continue... "

           invoke olesup "getClass" using by value theSafeArrayObj returning aClass
           perform get-class
      *    perform Getting-Info-About-a-SafeArray
           display ' ' upon syserr

*********************************** CoreReflection
      * The args array is an array of "PropertyValue" objects - create by invoking OO.o reflection
           display 'invoke ServManager "createInstance" using "com.sun.star.reflection.CoreReflection" returning CoreReflection' upon syserr
           invoke ServManager "createInstance" using "com.sun.star.reflection.CoreReflection" returning CoreReflection
           display 'CoreReflection "' CoreReflection '"' upon syserr
           display ' ' upon syserr

      * Is it really necessary to create the empty propertyvalue object like the foxpro example does and like oo seems to suggest?
      * The vbscript example doesn't need the Dim oPropertyValue.
      * loPropertyValue = CREATEOBJECT("Empty")
      *// create empty array of PropertyValue structs, needed for loadComponentFromURL
      *PropertyValue[] loadProps = new PropertyValue[0];

      * loCoreReflection.forName("com.sun.star.beans.PropertyValue").createobject(@loPropertyValue)
           display 'invoke CoreReflection "forName" using "com.sun.star.beans.PropertyValue" returning PropertyValue' upon syserr
           invoke CoreReflection "forName" using "com.sun.star.beans.PropertyValue" returning PropertyValue
           display 'PropertyValue "' PropertyValue '"' upon syserr

           invoke olesup "getClass" using by value PropertyValue returning aClass
           perform get-class
           display ' ' upon syserr

           display 'invoke PropertyValue "createObject" using "Empty" null returning ObjPropertyValue' upon syserr
           invoke PropertyValue "createObject" using "Empty" -1 returning ObjPropertyValue

      *     move 0 to v
      *     move -1 to VARIANT-VT-BOOL of v
      *     move VT-BOOL to VARIANT-vartype of v
      **    display 'invoke oleVariant "new" returning theVariantObj' upon syserr
      **    invoke oleVariant "new" returning theVariantObj
      *     display 'invoke oleVariant "newwithData" using v returning theVariantObj' upon syserr
      *     invoke oleVariant "newwithData" using v returning theVariantObj
      *     display 'invoke PropertyValue "createObject" using "ReadOnly" theVariantObj returning ObjPropertyValue' upon syserr
      *     invoke PropertyValue "createObject" using "ReadOnly" theVariantObj returning ObjPropertyValue

           move 0 to vData2
           move 0 to VARIANT-VT-I2 of vData2
           move VARIANT-VT-I2 to variant-vartype of vData2
           display 'invoke PropertyValue "createObject" using "ReadOnly" OLETRUE returning ObjPropertyValue' upon syserr
           invoke PropertyValue "createObject" using "ReadOnly" z"0" returning ObjPropertyValue

           display 'ObjPropertyValue "' ObjPropertyValue '"' upon syserr
           display ' ' upon syserr

      *    display 'invoke PropertyValue "setName" using "ReadOnly"' upon syserr
      *    invoke ObjPropertyValue "setPropertyValues" using "ReadOnly"
      *    display 'invoke PropertyValue "setValue" using 0' upon syserr
      *    invoke ObjPropertyValue "setValue" using 0 *> True = 1, False = 0

*********************************** _GetStruct
           display 'invoke ServManager "_GetStruct" using "com.sun.star.beans.PropertyValue" returning PropertyValue' upon syserr
           invoke ServManager "_GetStruct" using "com.sun.star.beans.PropertyValue" returning PropertyValue
           display 'PropertyValue "' PropertyValue '"' upon syserr

           invoke olesup "getClass" using by value PropertyValue returning aClass
           perform get-class
           display ' ' upon syserr

      *    can't call Getting-Info-About-a-SafeArray because it's not a safearray, its a COM object
      *    perform Getting-Info-About-a-SafeArray

           display 'invoke PropertyValue "setName" using "ReadOnly"' upon syserr
           invoke PropertyValue "setName" using "ReadOnly"
           display 'invoke PropertyValue "setValue" using 0' upon syserr
           invoke PropertyValue "setValue" using 0 *> True = 1, False = 0

           set theData to address of PropertyValue *> "putElement" reads data from an address pointer
           move 0 to iIndex(1)
           display 'invoke theSafeArrayObj "putOLEObject" ' upon syserr
           invoke theSafeArrayObj "putOLEObject"    *>   "putOLEObjectAsVariant"   "putOLEObject"
                                           using iIndex(1)
                                         by value PropertyValue *>theData  PropertyValue
                                       returning hResult
           display 'hResult  "' hResult  '"' upon syserr

*********************************** loadComponentFromURL

           display 'invoke StarDesktop "loadComponentFromURL"' upon syserr
      *    invoke StarDesktop "loadComponentFromURL" using by reference "C:\temp\test.ods"
      *    invoke StarDesktop "loadComponentFromURL" using by reference "file:///C:/temp/test.ods"
           invoke StarDesktop "loadComponentFromURL" using "private:factory/scalc"
                                                           "_blank"
                                                           0
                                                           theSafeArrayObj *> Array
      *                                                    PropertyValue *> Array
      *                                                    ObjPropertyValue *> Array
                                                     returning CalcDoc
           display 'CalcDoc "' CalcDoc '"' upon syserr

***********************************

      *>   I haven't tried to convert the rest yet.

      *>   Make Excel visible
           display 'invoke ExcelObject "setVisible" using by value 1 ' upon syserr
           invoke ExcelObject "setVisible" using by value 1

      *>   Get the collection of WorkBooks
           invoke ExcelObject "getWorkBooks" returning WorkBooksCollection

      *>   Add a new WorkBook to the collection
           invoke WorkBooksCollection "Add" returning WorkBook

           invoke ExcelObject "getCells" using by value 1 by value 1 returning Cell
           invoke Cell "setValue" using by reference z"test"
           invoke Cell "finalize" returning Cell

           invoke WorkBook "SaveAs" using by reference z"C:\TEMP\EXCLTEST.XLS"

      *>   Close the WorkBook.
           invoke WorkBook "Close" using by value 0

      *>   Finalize all objects
           invoke WorkBook "finalize" returning WorkBook
           invoke WorkBooksCollection "finalize" returning WorkBooksCollection

      *>   Exit Excel
           invoke ExcelObject "Quit"
           invoke ExcelObject "Finalize" returning ExcelObject

           GOBACK.

        get-class.

           if aClass = CharacterArray
               display "a CharacterArray" upon syserr
           else
               if aClass = olebase
                   display "A COM object" upon syserr
               else
                   if aClass = oleSafeArray
                       display "a SafeArray" upon syserr
                   else
                       if aClass = OLEVariant
                           display "a Variant" upon syserr
                       else
                           display "Not supported by COM Automation" upon syserr
                       end-if
                   end-if
               end-if
           end-if.

        Getting-Info-About-a-SafeArray.

       *>---Get the number of dimensions of the array
           display 'invoke theSafeArrayObj "getDim" returning dimensions' upon syserr
           invoke theSafeArrayObj "getDim" returning dimensions

           move 1 to dimensions
       *>---hResult is the Windows status code returned when the
       *>   SafeArray is queried. Zero indicates success, non-zero
       *>   indicates failure. Error codes are defined in copyfile
       *>   MFOLE.CPY, as level-78 data items.
           display 'invoke theSafeArrayObj "getLBound"' upon syserr
           invoke theSafeArrayObj "getLBound"
                                           using by value dimensions
                                               by reference lBound
                                       returning hResult
           display 'invoke theSafeArrayObj "getUBound"' upon syserr
           invoke theSafeArrayObj "getUBound"
                                           using by value dimensions
                                               by reference uBound
                                       returning hResult
       *>---Calculate the dimension size
           subtract lBound from uBound
           add 1 to uBound giving dimensionSize
       *>---Find out the type of data in the array
           display 'invoke theSafeArrayObj "getVarType" returning var-Type' upon syserr
           invoke theSafeArrayObj "getVarType" returning var-Type

           display "dimensions   " dimensions    upon syserr
           display "dimensionSize" dimensionSize upon syserr
           display "lBound       " lBound        upon syserr
           display "uBound       " uBound        upon syserr
           display "hResult      " hResult       upon syserr
           display "var-Type     " var-Type      upon syserr
           .

Re: How to call OO API using Microsoft Com Automation interface

Posted: Sun Mar 22, 2009 5:43 pm
by Ferda
I have been a COBOL (NetExpress) programmer for a long time.
The example you have given was most useful and many thanks for it.
However I could not find any code example of changing the background color of an excel cell.
Your help in this respect will be most welcome.
Thank you in advance.

Re: How to call OO API using Microsoft Com Automation interface

Posted: Mon Mar 23, 2009 8:38 pm
by dnelson
Here is the code to set the background color of an excel cell.

01 Interior object reference.

invoke Cell "getInterior" returning Interior
invoke Interior "setColorIndex" using by value 41 *> blue

Re: How to call OO API using Microsoft Com Automation interface

Posted: Tue Mar 24, 2009 12:52 am
by Villeroy
dnelson wrote:Here is the code to set the background color of an excel cell.
Wrong forum. OOo Calc is not Excel. Calc is just a part of a platform independent application with a completely different API.

Normally you don't need any code to apply a formatting attribute or combinations of attributes. Function STYLE and conditional formatting can do this reliably and fast without writing any additional code. Of course this requires that you know how to work with the application, utilizing templates and styles rather than hacking on blank documents.

All the questions in this thread have nothing to do with Excel, Calc or any particular programming language. You can access the very same API from any language in the same way if you know how to access properties methods in your particular language. There is plenty of documentation, example code and introspection tools. My favourite inspector is http://extensions.services.openoffice.org/project/MRI
Install it, restart the office, select some cell(s) and call menu:Tools>Add-Ons> "MRI <--selection".
A cell, a range and even a multiple selection of ranges, in Calc as well as in Writer tables, support service com.sun.star.table.CellProperties which provides property CellBackColor: http://api.openoffice.org/docs/common/r ... lBackColor

Re: How to call OO API using Microsoft Com Automation interface

Posted: Fri Jun 12, 2009 11:03 am
by OlivierS
here is a small program for cobol microfocus netexpress with a successful call to openoffice write

$set ooctrl(+P)
IDENTIFICATION DIVISION.
ENVIRONMENT DIVISION.

class-control.
OServManager is class "$OLE$com.sun.star.ServiceManager"
oleSafeArray is class "olesafea" OLE SafeArray class
.

DATA DIVISION.
WORKING-STORAGE SECTION.

copy mfole.cpy.


*---SafeArray dimension structure SafeArrayBound
* celements is the total number of elements
* lBound is the lower boundary (usually 0)
01 tagSafeArrayBound is typedef.
05 celements usage ULONG.
05 llbound usage LONG.
01 SafeArrayBound is typedef usage tagSafeArrayBound.
01 saBound SafeArrayBound.


01 ServManager object reference.
01 StarDesktop object reference.
01 aDoc object reference.
01 atext object reference.

01 Group1SafeArray object reference.

01 theVarType VARTYPE.

01 cDims UINT.

*-----------------------------------------------------------------
PROCEDURE DIVISION.

Start-Up. # Start-Up
*--- create the document
perform CreateDocument-Stup.

*--- fill the document with some text
perform FillDocument-Stup.

exit program.
stop run.

*-----------------------------------------------------------------
CreateDocument-Stup. # CreateDocument-Stup
*---
invoke OServManager "new" returning ServManager

*---
invoke ServManager "createInstance"
using "com.sun.star.frame.Desktop"
returning StarDesktop.

*--- olesafearray
move VT-VARIANT to thevarType.
move 1 to cDims.
move 0 to cElements OF saBound.
move 0 to llBound OF saBound.
invoke olesafearray "new" using by value thevarType
by value cDims
by reference saBound
returning Group1SafeArray.

*--- loadComponentFromURL
invoke StarDesktop "loadComponentFromURL"
using "private:factory/swriter" change 'swriter' with 'scalc' to call openOffice Calc
"_blank"
"0"
Group1SafeArray
returning aDoc.

*-----------------------------------------------------------------
FillDocument-Stup. # FillDocument-Stup
invoke aDoc "GetText" returning aText. remove this line if you call 'Calc'
invoke aText "SetString" using "Hello World". remove this line if you call 'Calc'

*-----------------------------------------------------------------

Re: How to call OO API using Microsoft Com Automation interface

Posted: Fri Jun 12, 2009 11:27 pm
by dnelson
Many thanks to Olivier Stainier of Ollysoft who finally cracked this code! Eight months later I finally have the solution! I am absolutely astounded at how close the solution was and yet how much it continued to escape me. In comparing Olivier's sample code (that I am so grateful for) to the sample program (calctst4) that I had posted on the forum on Jan 24, 2009, I was able to determine that the only thing I needed to do to get my sample program to work was to put the zero (the third parameter in the call to loadComponentFromURL) in double quotes as "0". That's it! I had spent countless hours trying to decipher the error message returned by OpenOffice.

invoke StarDesktop "loadComponentFromURL" using "private:factory/swriter" "_blank" "0" theSafeArrayObj returning aDoc *> This works!
invoke StarDesktop "loadComponentFromURL" using "private:factory/swriter" "_blank" 0 theSafeArrayObj returning aDoc *> This does not work and returns the following error:

Exception 65537 not trapped by the class oleexceptionmanager.
Description: "Server defined OLE exception"
(80020009): InterfaceOleWrapper_Impl::Invoke :
[automation bridge]UnoConversionUtilities<T>::variantToAny
Cannot convert the value of vartype :"8" to the expected UNO type of type class: 6

I had always assumed that the error message was somehow objecting to the way I had my safe array defined (the fourth parameter in the call to loadComponentFromURL), but my safe array was just fine. I literally just had to put the double quotes around the zero and everything worked.

Olivier - how did you ever discover the need for the double quotes? Every example that I had ever looked at did not have the zero enclosed in double quotes. There are examples in C++, VB, VB script, FoxPro and other languages and none of them have double quotes around the zero (including all of the examples provided in the OpenOffice Developer's Guide (1250 pages)). Notice the following example from the Developer's Guide (page 165):

'Open a new empty writer document
Dim args()
Set objDocument= objDesktop.loadComponentFromURL("private:factory/swriter", "_blank", 0, args)

I was also able to get short programs to work with both writer and calc. As I get time, I will continue my efforts to convert some of our more complex programs from Microsoft Office (Word and Excel) to OpenOffice.

Re: How to call OO API using Microsoft Com Automation interface

Posted: Mon Jun 15, 2009 6:55 pm
by dnelson
I opened an incident with the vendor (Micro Focus) and here is their response:

In your VB example:
Set objDocument= objDesktop.loadComponentFromURL("private:factory/swriter", "_blank", 0, args)

The third parameter 0 will be treated as an integer and will be passed on the stack as “value type”.

In Net Express COBOL:
invoke StarDesktop "loadComponentFromURL" using "private:factory/swriter" "_blank" 0 theSafeArrayObj returning aDoc

The third parameter 0 will be treated as a 32-bit integer, but it will be passed by reference as this is the COBOL default if nothing is specified.

So there is a problem with the run-time system in converting an integer passed by reference to an integer passed by value. When the quotes are added to make “0” the parameter is no longer an integer, but it is instead a one byte string passed by reference. The runtime seems to handle this conversion OK.

The correct syntax should actually be:
invoke StarDesktop "loadComponentFromURL"
using by reference "private:factory/swriter"
by reference "_blank"
By value 0
By reference theSafeArrayObj
returning aDoc

Re: How to call OO API using Microsoft Com Automation interface

Posted: Fri Nov 27, 2009 3:37 pm
by GregMcC
Great thread - This sounds 'Fixed' - if you ( DNelson ) thinks it is sorted / resolved then could you change the status on the header of this thread ...

Thanks Greg :)

Re: How to call OO API using Microsoft Com Automation interface

Posted: Fri Nov 27, 2009 5:25 pm
by dnelson
I have continued to do further development in Net Express COBOL using the OpenOffice API and it seems to be stable and I'm finding it to be a lot more robust than I had originally anticipated.

Re: [Solved] Call OOo API using MS Com Automation interface

Posted: Tue Dec 06, 2016 3:35 pm
by ronaldo_dachi
I have been a COBOL (NetExpress) programmer for a long time.
The example you have given was most useful and many thanks for it.
However I could not find any code example of changing the borders of an excel cell.
Your help in this respect will be most welcome.
Thank you in advance.

Re: [Solved] Call OOo API using MS Com Automation interface

Posted: Tue Dec 06, 2016 4:53 pm
by dnelson
Your post is not very specific. Are you wanting to change the borders by making them bold or are you wanting to change the borders by combining cells, or something else?

Have you tried doing the tasks manually and recording a macro in Excel (assuming that what you're intending to do in Excel can also be done in Calc) so that you can look at the VB code? It usually is helpful to me to do things in Excel before doing them in Calc (again, assuming you have Excel).

Re: [Solved] Call OOo API using MS Com Automation interface

Posted: Tue Dec 06, 2016 5:02 pm
by ronaldo_dachi
I would like to put borders in the cell

Re: [Solved] Call OOo API using MS Com Automation interface

Posted: Tue Dec 06, 2016 5:05 pm
by ronaldo_dachi
I am using cobol net express 3.1

Re: [Solved] Call OOo API using MS Com Automation interface

Posted: Tue Dec 06, 2016 5:08 pm
by dnelson
I haven't used Net Express 3.1 for more than 10 years so I'm not sure what all is available. I don't have much time to help you right now (unfortunately). We are using Net Express 5.1 and will be converting to Visual COBOL 2.3 soon. I know there are a lot of bugs in 3.1 that have been fixed - some specific to the COM automation interface.