[Solved] Open a Spreadsheet via OLE

[Peter18]

A firendly hallo to anybody,

I hope somebody will help me to get started.

I want to open a Spreadsheet from Delphi, in a Windows environment, to read Data via OLE. With MS-Office no problem. I hoped there is a compatible interface, because several Macros in Excel-Sheets are running with Calc. But Objekts and Methodes are too different.

I tried to record a macro with Calc opening a Excel file, but it did not work.

For example this is the way to get an Excel-Sheet:

Code: Select all

    excel := CreateOleObject('Excel.Application');
    excel.Workbooks.Open( FileName );
    Sheet := excel.ActiveWorkbook.Worksheets[SheetName];

To create the OLE-Object with the String 'com.sun.star.ServiceManager' worked, but the next steps failed and I did not find a solution in the API-Doc of OO.

It would be nice, if anyone would show me the way to start.

Thank you to all
Peter

[Villeroy]

In central Europe Google is online again.

[rudolfo]

There is an introduction site for OLE Automating OpenOffice.org. The sample code is for VisualBasic (Script/for Applications) but the OLE objects are by concept the same for VBScript, Delphi ...
Not sure if they mention it on that page. But the COM object of OpenOffice doesn't come with a Typelib library file, so early binding and completion of object methods or properties is not possible.

[rudolfo]

Just looking at your initial post again ... the word spreadsheet is used to often.
Can you please confirm that you mean OLE/COM automation and not simply OLE embedding of files. For the latter one it is fully up to the OS if it starts MS Excel for an embedded .xls file object or OOo Calc.
And you better look for help for your problem in the macro forum (particularly the subforum External Programs).
This forum is for questions related to the thousands of extensions available at http://extensions.services.openoffice.org/

[Peter18]

Hallo Villeroy,

thank you for your answer. Verry helpfull, great! (if I am stupid) It's the same if you answer someone who asks in the center of a big forest for the direction to the next village or city with: "Direction I think about." Is it important to understand how a motor works, if you want to dirve a car?

I asked for the direction, a keyword or a short example. By the way, thank you rudolfo the keywords eary binding and late binding may be helpfull, I'll follow them.

It would be nice if anybody has a short example or a link to one.

Greetings
Peter

[B Marcelly]

Hi,
You have to learn the basics of OpenOffice.org API first.
You can do this by reading the Basic Programming Guide and writing some OpenOffice.org Basic macros.

Then you can easily translate the programming principles into Delphi + COM-Automation.
The Delphi 7 OOo tool will help you, get it from this page.
But learn OpenOffice.org API first.

Oh, and remember that COM-Automation is bugged in OOo 3.3. You have time to learn some API until OOo 3.4 is finalized.

[Peter18]

hallo Bernard,

thank you for your answer. The links will be helpful.

My experience tells me, it is more easy to understand a description if you have an example you can work with. While I prepared me for certifications from novell and micrsoft I had to read the books twice or more to pass the examination. If you have got an example you can work with and modify to understand the background, you have to read it only once.

In this case I don't need the whole package, but open a spereadsheet, read the data and send it to an other application. May be I need more later on. But for the moment I would like to show: it works.

I hope somebody will give me an example for an easier begining. The first steps are most difficult.

Greetings
Peter

[Peter18]

Hello to anybody,

I found the command:

Code: Select all

StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array())

My question is, if I replace "_blank" with path and filename, will I open the existing file?

Thank you

Peter

[rudolfo]

It seems like you haven't read the Basic Programming Guide as recommended by Bernard in his post. The third or fourth chapter about Working with Documents has what you need to open Documents via macros (and it has quite a lot of sample code, as well).

You will figure out that it is the first parameter of loadComponentFromURL, the URL parameter, that you have to change:
"private:factory/scalc" --> "file:///c:/test.ods"
Usually you don't build the URI file:... manually but you feed an OS file name into ConvertToURL (again all about that is in the mentioned chapter).
With one caveat: ConvertToURL is a built-in of OpenOffice Basic and not available in Delphi, VBScript or any other language used for COM Automation. But I guess there is a workaround in the Delphi 7 OOo Tools package mentioned by Bernard.

[Peter18]

Hello rudolfo,

thank You for your answer. This info will help me. If you read any Guide, it is writen from people who knows that they are talking about. If you work with Microsoft you learn how to understand the description. If you then come to Unix there is a completly different thinking and you have to learn to understand what is written in the guide.

With your information I have a point where I can start. Now I can open a file (I hope) and try to read data and then more, step by step. If a test will not work I can read again and try it in an other way. At the end I can do the things I want to do.

The start is always the bigest problem.

Thank you again and a nice weekend.

Peter