Visual Studio 2005 (VB .net) and OOo Calc automation...

oonewb · General User Joined: 24 May 2007 Posts: 6

Hello,

I'm having a lot of trouble finding information on writing code to get VB.net to control Calc. (open the program, create / size / fill cells, etc...) Basically my program just takes text it gathers from textboxes and prints everything to a spreadsheet. (currently working with MS Excel, but our customers need a more cost effective solution, hopefully openoffice.org)

I have tried simply copying and pasting code from some of the help files posted around here but I always get an error.

For example I tried this:

Sub firstOOoProc()

Dim oSM As Object 'Root object for accessing OpenOffice from VB
Dim oDesk, oDoc As Object 'First objects from the API
Dim arg() 'Ignore it for the moment !

'Instanciate 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")

'Create a new doc
oDoc = oDesk.loadComponentFromURL("private:factory/swriter", "_blank", 0, arg)
'Close the doc
oDoc.Close(True)
oDoc = Nothing

'Open an existing doc (pay attention to the syntax for first argument)
oDoc = oDesk.loadComponentFromURL("file:///c:/dev/ooo/test.doc", "_blank", 0, arg)
'Save the doc
Call oDoc.storeToURL("file:///c:/dev/ooo/test2.sxw", arg)
'Close the doc
oDoc.Close(True)
oDoc = Nothing

End Sub

When I try to run it (tab through the code) I get this error:

"Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))"

or in other words: "The exception that is thrown when an unrecognized HRESULT is returned from a COM method call. "

I'm not sure what this means, but Visual Studio acts like the code is simply invalid. Is this because I need to "Add Reference" for openoffice, or "Import" like you have when connecting to MS Excel?

ie. "Imports Microsoft.Office.Interop.Excel"

Anyways... I could really use some help. Thanks!

oonewb · General User Joined: 24 May 2007 Posts: 6

Well... I think I've narrowed it down a bit. This is the line that seems to be pulling this error:

oDoc = oDesk.loadComponentFromURL("private:factory/scalc", "_blank", 0, dummy)

So does anyone know what I'm missing here? Would it have anything to do with the fact that I'm running Vista?

EDIT/Update: Tried code on XP, same prob. I also tried Importing the "SO_ActiveXLib" and adding the reference, which did not work either.

Is this even possible? Does anyone have any ideas at all?!

oonewb · General User Joined: 24 May 2007 Posts: 6

Okay... so here's the deal.

I am using Visual Studio 2005 with VB.net.

The problem is this code is written for VB6, so I am getting this error because I'm trying to run VB6 code in VB.net.

So... now I ask... Does anyone know how to automate OpenOffice with VB.net??

oonewb · General User Joined: 24 May 2007 Posts: 6

FIXED!!

This code now runs on VB.net. (opens a new calc sheet)

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim oSM As Object
Dim oDesk As Object
Dim oDoc As Object

oSM = CreateObject("com.sun.star.ServiceManager")
oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
Dim aNoArgs(-1)
oDoc = oDesk.loadComponentFromURL("private:factory/scalc", "_blank", 0, aNoArgs)

End Sub

End Class

pitonyak · Posted: Fri May 25, 2007 7:09 pm Post subject:

Very impressive, you solved your own question! Very nice! Thanks for posting the solution.

Many people here do not have access to Visual studio, especially at home.
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php

FrankO · Newbie Joined: 13 Jun 2007 Posts: 4

Well done oonewb, you are the only person i have come across that has posted anything worthwhile on this issue. Thanks!

I have been using Excel but now switched to OOo. In my code i currently have

Azoto · Newbie Joined: 27 Jun 2007 Posts: 1

oonewb you're GREAT!! I spent my whole day trying to find a solution to the same problem you had. Till I found your post. Thank you!

martonx · Newbie Joined: 13 May 2008 Posts: 1

Thank you oonewb!

And how is going on? How can i add range, values etc...

Is anywhere some useful documentation for OpenOffice automation?

hffm · Posted: Fri May 23, 2008 9:19 pm Post subject:

hello
I posted this solution a bit time ago...
see:http://www.oooforum.org/forum/viewtopic.phtml?t=44760
In facts if you're working with vb net via com automation it's the same as with vb6, the differences are not from oo part but from Vb parts: with vb net you have to instanciate objects and so on, so if you're trying to call a null arguments (aNoArgs) you have to delcare and to initialize it (-1).
Sorry for my english

therealtoecutter · Posted: Mon Aug 03, 2009 1:46 pm Post subject:

therealtoecutter · Posted: Mon Aug 03, 2009 9:39 pm Post subject:

got it to work finally by conveting the file address to a url