[Solved] OpenOffice with visual basic.net

Creating a macro - Writing a Script - Using the API

[Solved] OpenOffice with visual basic.net

Postby visomes » Mon Aug 14, 2017 3:56 pm

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   Expand viewCollapse view
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
visomes
 
Posts: 9
Joined: Mon Aug 14, 2017 3:50 pm

Re: OpenOffice with visual basic.net

Postby FJCC » Mon Aug 14, 2017 4:50 pm

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())
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6157
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: OpenOffice with visual baisc.net

Postby visomes » Mon Aug 14, 2017 4:59 pm

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: 9
Joined: Mon Aug 14, 2017 3:50 pm

Re: OpenOffice with visual basic.net

Postby visomes » Mon Aug 14, 2017 6:06 pm

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

Re: OpenOffice with visual basic.net

Postby visomes » Mon Aug 14, 2017 6:09 pm

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
visomes
 
Posts: 9
Joined: Mon Aug 14, 2017 3:50 pm

Re: OpenOffice with visual basic.net

Postby FJCC » Mon Aug 14, 2017 6:21 pm

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   Expand viewCollapse view
arg() = Array()
oDoc.storeToURL("file:///C:/Users/PikachU/Desktop/Importar/as.ods", arg())

I thought your declaration of arg as
Code: Select all   Expand viewCollapse view
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().
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6157
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: OpenOffice with visual basic.net

Postby visomes » Mon Aug 14, 2017 6:32 pm

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


Code: Select all   Expand viewCollapse view
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
visomes
 
Posts: 9
Joined: Mon Aug 14, 2017 3:50 pm

Re: OpenOffice with visual basic.net

Postby RoryOF » Mon Aug 14, 2017 6:43 pm

In OpenOffice.org Macros Explained.odt section 12.4.6 Loading and Saving Documents[/url]
Andrew Pitonyak uses
Code: Select all   Expand viewCollapse view
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.4 on Xubuntu 16.04.03 (mostly 64 bit version) and infrequently on Win2K/XP
14 October 2016 was Pooh's 90th birthday
User avatar
RoryOF
Moderator
 
Posts: 25492
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: OpenOffice with visual basic.net

Postby visomes » Mon Aug 14, 2017 6:50 pm

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
visomes
 
Posts: 9
Joined: Mon Aug 14, 2017 3:50 pm

Re: OpenOffice with visual basic.net

Postby Villeroy » Mon Aug 14, 2017 6:57 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24652
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: OpenOffice with visual basic.net

Postby JohnSUN-Pensioner » Mon Aug 14, 2017 6:59 pm

... 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.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 755
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: OpenOffice with visual basic.net

Postby visomes » Mon Aug 14, 2017 7:34 pm

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
visomes
 
Posts: 9
Joined: Mon Aug 14, 2017 3:50 pm

Re: OpenOffice with visual basic.net

Postby Texmanage » Fri Nov 10, 2017 11:49 am

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

Postby Texmanage » Fri Nov 10, 2017 11:51 am

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
Texmanage
 
Posts: 8
Joined: Fri Nov 10, 2017 11:15 am

Re: OpenOffice with visual basic.net

Postby visomes » Fri Nov 10, 2017 5:07 pm

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
visomes
 
Posts: 9
Joined: Mon Aug 14, 2017 3:50 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests