[Solved] OpenOffice with visual basic.net

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
visomes
Posts: 11
Joined: Mon Aug 14, 2017 3:50 pm

[Solved] OpenOffice with visual basic.net

Post by visomes »

Good morning people, everything fine?
I am trying to feed a spreadsheet in Open Office using Visual Basic.net 2008, I am using the programming below, but this one giving several errors, can someone help me, thanks ..

Code: Select all

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oSheet As Object
        Dim oSM                   '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")

        'Open an existing doc (pay attention to the syntax for first argument)
        oDoc = oDesk.loadComponentFromURL("file:///C:\Users\Supervisor\Desktop\testeopen\1\as.ods", "_blank", 0, arg())
        'oDoc = oDesk.loadComponentFromURL("file:///C:\Users\PikachU\Desktop\Importar\as.ods", "_blank", 0, )


        oSheet = oDoc.getSheets().getByIndex(0) 'seleciona primeira planilha

        Call oSheet.getCellByPosition(3, 1).SetValue(111) 'seta valor col,linha
        Call oSheet.getCellByPosition(3, 2).SetValue(222) 'seta valor col,linha
        Call oSheet.getCellByPosition(3, 3).SetString("Marcos") 'seta valor da celular do tipo string
        Call oSheet.getCellByPosition(3, 5).SetFormula("=SUM(D2:D4)") 'seta celula do tipo formula
        'Save the doc
        'Call oDoc.storeToURL("file:///C:\Users\Supervisor\Desktop\testeopen\\as.ods")
        Call oDoc.storeToURL("file:///C:\Users\PikachU\Desktop\Importar\as.ods", arg())
        oDoc.Close(True)
        oDoc = Nothing

        MsgBox("ok")

    End Sub

End Class
Last edited by Hagar Delest on Sun Nov 12, 2017 12:21 am, edited 1 time in total.
Reason: tagged [Solved].
Open office 4.3.1
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: OpenOffice with visual basic.net

Post by FJCC »

1. Please tells us where the errors happen.

2. Your URLs are not written correctly. URLs always use forward slashes (/). Change

oDoc = oDesk.loadComponentFromURL("file:///C:\Users\Supervisor\Desktop\testeopen\1\as.ods", "_blank", 0, arg())

to

oDoc = oDesk.loadComponentFromURL("file:///C:/Users/Supervisor/Desktop/testeopen/1/as.ods", "_blank", 0, arg())
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
visomes
Posts: 11
Joined: Mon Aug 14, 2017 3:50 pm

Re: OpenOffice with visual baisc.net

Post by visomes »

Errors occur at the end of the command:

Call oDoc.storeToURL("file:///C:/Users/PikachU/Desktop/Importar/as.ods", arg())

The error appears below this way:


Error 1 Number of indices is less than the number of dimensions of the indexed array. C:\Users\Supervisor\Desktop\teset\ExportacaoBrOffice\ExportacaoBrOffice\Form1.vb 23 117 ExportacaoBrOffice
Open office 4.3.1
visomes
Posts: 11
Joined: Mon Aug 14, 2017 3:50 pm

Re: OpenOffice with visual basic.net

Post by visomes »

This is error because arg())
Open office 4.3.1
visomes
Posts: 11
Joined: Mon Aug 14, 2017 3:50 pm

Re: OpenOffice with visual basic.net

Post by visomes »

How much do I remove the arg () variable, leaving it this way:

oDoc = oDesk.loadComponentFromURL("file:///C:/Users/Supervisor/Desktop/testeopen/1/as.ods", "_blank", 0)

Presents another error:

Type not matching. (Exception of HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
Open office 4.3.1
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: OpenOffice with visual basic.net

Post by FJCC »

Yes, I suspect the error is due to the arg() parameter. You certainly can't just remove it from the function call. In OpenOffice Basic, it needs to be an array, though it can be empty, so I would write

Code: Select all

arg() = Array()
oDoc.storeToURL("file:///C:/Users/PikachU/Desktop/Importar/as.ods", arg())
I thought your declaration of arg as

Code: Select all

Dim arg() 
would do the same thing but apparently not. If you can't get an empty array to work, try setting some harmless property among the ones listed here, maybe Author or Comment. To do that, you will have to figure out how to create an array of length 1 of com.sun.star.beans.PropertyValue in VB.net. Set the Name of the Property Value to Author or Comment and set the Value to whatever you want. Then pass that array as the arg() to storeToURL().
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
visomes
Posts: 11
Joined: Mon Aug 14, 2017 3:50 pm

Re: OpenOffice with visual basic.net

Post by visomes »

I did the form below, declaring arg (0), but it does not work yet. What would that matrix look like?

Code: Select all

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oSheet As Object
        Dim oSM                   'Root object for accessing OpenOffice from VB
        Dim oDesk, oDoc As Object 'First objects from the API
        Dim arg(0)            'Ignore it for the moment !
        arg(0).Name = "Hidden"
        arg(0).Value = True
        '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")

        'Open an existing doc (pay attention to the syntax for first argument)
        oDoc = oDesk.loadComponentFromURL("file:///C:/Users/Supervisor/Desktop/testeopen/1/as.ods", "_blank", 0, arg())



        oSheet = oDoc.getSheets().getByIndex(0) 'seleciona primeira planilha

        Call oSheet.getCellByPosition(3, 1).SetValue(111) 'seta valor col,linha
        Call oSheet.getCellByPosition(3, 2).SetValue(222) 'seta valor col,linha
        Call oSheet.getCellByPosition(3, 3).SetString("Marcos") 'seta valor da celular do tipo string
        Call oSheet.getCellByPosition(3, 5).SetFormula("=SUM(D2:D4)") 'seta celula do tipo formula
        'Save the doc

        Call oDoc.storeToURL("file:///C:/Users/PikachU/Desktop/Importar/as.ods", arg())

        oDoc.Close(True)
        oDoc = Nothing

        MsgBox("ok")

    End Sub
Open office 4.3.1
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: OpenOffice with visual basic.net

Post by RoryOF »

In OpenOffice.org Macros Explained.odt section 12.4.6 Loading and Saving Documents[/url]
Andrew Pitonyak uses

Code: Select all

Dim args(0) as new com.sun.star.beans.PropertyValue
args(0).Name = "FilterName"
args(0).Value = "writer_pdf_Export"
ThisComponent.storeToURL("file:///test.pdf",args()) 
Further information can be found in that work.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
visomes
Posts: 11
Joined: Mon Aug 14, 2017 3:50 pm

Re: OpenOffice with visual basic.net

Post by visomes »

Hello, I used this documentation for a code, but I still can not solve it. Still thank you for the tip.
Open office 4.3.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: OpenOffice with visual basic.net

Post by Villeroy »

Dim arg(0) As New com.sun.star.beans.PropertyValue
Last edited by Villeroy on Mon Aug 14, 2017 7:04 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: OpenOffice with visual basic.net

Post by JohnSUN-Pensioner »

... and if you save document as .ods then FilterName will be "calc8"
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
visomes
Posts: 11
Joined: Mon Aug 14, 2017 3:50 pm

Re: OpenOffice with visual basic.net

Post by visomes »

How do I insert the open office reference, not visual basic.net. How do I insert the open office reference, not visual basic.net.
Open office 4.3.1
Texmanage
Posts: 8
Joined: Fri Nov 10, 2017 11:15 am

Re: OpenOffice with visual basic.net

Post by Texmanage »

Here is your original code.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim oSheet As Object
Dim oSM '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")

'Open an existing doc (pay attention to the syntax for first argument)
oDoc = oDesk.loadComponentFromURL("file:///C:\Users\Supervisor\Desktop\testeopen\1\as.ods", "_blank", 0, arg())
'oDoc = oDesk.loadComponentFromURL("file:///C:\Users\PikachU\Desktop\Importar\as.ods", "_blank", 0, )


oSheet = oDoc.getSheets().getByIndex(0) 'seleciona primeira planilha

Call oSheet.getCellByPosition(3, 1).SetValue(111) 'seta valor col,linha
Call oSheet.getCellByPosition(3, 2).SetValue(222) 'seta valor col,linha
Call oSheet.getCellByPosition(3, 3).SetString("Marcos") 'seta valor da celular do tipo string
Call oSheet.getCellByPosition(3, 5).SetFormula("=SUM(D2:D4)") 'seta celula do tipo formula
'Save the doc
'Call oDoc.storeToURL("file:///C:\Users\Supervisor\Desktop\testeopen\\as.ods")
Call oDoc.storeToURL("file:///C:\Users\PikachU\Desktop\Importar\as.ods", arg())
oDoc.Close(True)
oDoc = Nothing

MsgBox("ok")

End Sub

End Class

if this line generates error, Call oDoc.storeToURL("file:///C:\Users\PikachU\Desktop\Importar\as.ods", arg()) showing error. just replace this coding to Call oDoc.store(). It wont show Type Mismatch error.
OpenOffice 3.1 on Windows 7
Texmanage
Posts: 8
Joined: Fri Nov 10, 2017 11:15 am

Re: OpenOffice with visual basic.net

Post by Texmanage »

change this line "Call oDoc.storeToURL("file:///C:/Users/PikachU/Desktop/Importar/as.ods", arg())" to "Call oDoc.Store()". It will save the file.
OpenOffice 3.1 on Windows 7
visomes
Posts: 11
Joined: Mon Aug 14, 2017 3:50 pm

Re: OpenOffice with visual basic.net

Post by visomes »

Texmanage wrote:Here is your original code.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim oSheet As Object
Dim oSM '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")

'Open an existing doc (pay attention to the syntax for first argument)
oDoc = oDesk.loadComponentFromURL("file:///C:\Users\Supervisor\Desktop\testeopen\1\as.ods", "_blank", 0, arg())
'oDoc = oDesk.loadComponentFromURL("file:///C:\Users\PikachU\Desktop\Importar\as.ods", "_blank", 0, )


oSheet = oDoc.getSheets().getByIndex(0) 'seleciona primeira planilha

Call oSheet.getCellByPosition(3, 1).SetValue(111) 'seta valor col,linha
Call oSheet.getCellByPosition(3, 2).SetValue(222) 'seta valor col,linha
Call oSheet.getCellByPosition(3, 3).SetString("Marcos") 'seta valor da celular do tipo string
Call oSheet.getCellByPosition(3, 5).SetFormula("=SUM(D2:D4)") 'seta celula do tipo formula
'Save the doc
'Call oDoc.storeToURL("file:///C:\Users\Supervisor\Desktop\testeopen\\as.ods")
Call oDoc.storeToURL("file:///C:\Users\PikachU\Desktop\Importar\as.ods", arg())
oDoc.Close(True)
oDoc = Nothing

MsgBox("ok")

End Sub

End Class

if this line generates error, Call oDoc.storeToURL("file:///C:\Users\PikachU\Desktop\Importar\as.ods", arg()) showing error. just replace this coding to Call oDoc.store(). It wont show Type Mismatch error.
Open office 4.3.1
Post Reply