[Solved] Open a Spreadsheet via OLE

Java, C++, C#, Delphi, ??? - Using the UNO bridges

[Solved] Open a Spreadsheet via OLE

Postby Peter18 » Thu May 12, 2011 6:49 pm

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   Expand viewCollapse view
    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
Last edited by Peter18 on Fri Oct 07, 2011 1:12 pm, edited 1 time in total.
OpenOffice 3.3; OpenOffice 4.1.1
Peter18
 
Posts: 99
Joined: Thu May 12, 2011 1:01 pm

Re: Open a Spreadsheet via OLE

Postby Villeroy » Thu May 12, 2011 7:47 pm

In central Europe Google is online again.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Open a Spreadsheet via OLE

Postby rudolfo » Thu May 12, 2011 9:48 pm

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.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Open a Spreadsheet via OLE

Postby rudolfo » Thu May 12, 2011 9:57 pm

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/
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Open a Spreadsheet via OLE

Postby Peter18 » Fri May 13, 2011 2:16 pm

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
OpenOffice 3.3; OpenOffice 4.1.1
Peter18
 
Posts: 99
Joined: Thu May 12, 2011 1:01 pm

Re: Open a Spreadsheet via OLE

Postby B Marcelly » Fri May 13, 2011 3:15 pm

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.
Bernard

OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
B Marcelly
Volunteer
 
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Open a Spreadsheet via OLE

Postby Peter18 » Thu May 26, 2011 4:15 pm

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
OpenOffice 3.3; OpenOffice 4.1.1
Peter18
 
Posts: 99
Joined: Thu May 12, 2011 1:01 pm

Re: Open a Spreadsheet via OLE

Postby Peter18 » Thu May 26, 2011 4:31 pm

Hello to anybody,

I found the command:
Code: Select all   Expand viewCollapse view
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
OpenOffice 3.3; OpenOffice 4.1.1
Peter18
 
Posts: 99
Joined: Thu May 12, 2011 1:01 pm

Re: Open a Spreadsheet via OLE

Postby rudolfo » Fri May 27, 2011 1:24 am

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.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Open a Spreadsheet via OLE

Postby Peter18 » Fri May 27, 2011 5:29 pm

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
OpenOffice 3.3; OpenOffice 4.1.1
Peter18
 
Posts: 99
Joined: Thu May 12, 2011 1:01 pm


Return to External Programs

Who is online

Users browsing this forum: No registered users and 1 guest