[Solved] Program AOO 4.0 with VB.NET

The Application Programming Interface and the OASIS Open Document Format

[Solved] Program AOO 4.0 with VB.NET

Postby ChanHuynh » Mon Jun 08, 2020 4:53 am

Hi,
I'm new to OpenOffice. I spent whole day try to develop the application that can open spreadsheet and save it as PDF. it seem like I couldn't make the save file successfully.
I tried to reference the .dll for my program but I couldn't find any "cli_" except "cli_uno" in "OpenOffice 4\program\cli_uno.dll". So I have to use cli_ure.dll, cli_uretypes.dll, cli_oootypes.dll, cli_cppuhelper.dll, cli_basetypes.dll from 3.0.0.0 version

Below his my code

Code: Select all   Expand viewCollapse view
Imports unoidl.com.sun.star.lang
Imports unoidl.com.sun.star.uno
Imports unoidl.com.sun.star.bridge
Imports uno.util
Imports System
Imports System.Collections

Dim FileName As String = "C:/ExcelSheet.xls"

    Sub Main()
        AOOXL(FileName)
    End Sub

    Private Sub AOOXL(ByVal XLFileLoc As String)

        Dim PDFFileName As String = "C:/SSDemo.pdf"

        Try
            Dim xContext As XComponentContext = Bootstrap.bootstrap()
            Dim xFactory As XMultiServiceFactory = DirectCast(xContext.getServiceManager(), XMultiServiceFactory)
            Dim xDesktop As unoidl.com.sun.star.frame.XDesktop = DirectCast(xFactory.createInstance("com.sun.star.frame.Desktop"), unoidl.com.sun.star.frame.XDesktop)
            Dim xComponentLoader As unoidl.com.sun.star.frame.XComponentLoader = DirectCast(xDesktop, unoidl.com.sun.star.frame.XComponentLoader)

            Dim arProps() As unoidl.com.sun.star.beans.PropertyValue = New unoidl.com.sun.star.beans.PropertyValue() {}

            'Open XL
            Dim xComponent As unoidl.com.sun.star.lang.XComponent = xComponentLoader.loadComponentFromURL(ConvertFile(XLFileLoc), "_blank", 0, arProps)

            Dim ar() As unoidl.com.sun.star.beans.PropertyValue = New unoidl.com.sun.star.beans.PropertyValue(1) {}
            ar(0) = New unoidl.com.sun.star.beans.PropertyValue()
            ar(0).Name = "FilterName"
            ar(0).Value = New uno.Any("calc_pdf_Export")

            Dim xDispatcher As unoidl.com.sun.star.frame.XDispatchHelper

            [b]xDispatcher.executeDispatch(xComponent, ConvertFile(PDFFileName), "", "", ar)[/b]

            xDesktop.terminate()

        Catch XLError As System.Exception
            MsgBox(XLError.Message)
        End Try

    End Sub

    Private Function ConvertFile(ByVal cvrFilePath As String) As String
        Return "file:///" & cvrFilePath
    End Function


Everything works except the line
Code: Select all   Expand viewCollapse view
xDispatcher.executeDispatch(xComponent, ConvertFile(PDFFileName), "", "", ar)
where I intended to save the spreadsheet file. I looked up many places and find save as "StoreURL" but my library .dll doesn't support "StoreURL". Could anyone help me this case ? really appreciated !
Attachments
reference.JPG
Reference detail
Last edited by ChanHuynh on Wed Jun 10, 2020 3:10 am, edited 2 times in total.
OpenOffice 4.0
Windows 10
ChanHuynh
 
Posts: 7
Joined: Mon Jun 08, 2020 4:34 am

Re: Program AOO 4.0 with VB.NET

Postby Zizi64 » Mon Jun 08, 2020 6:19 am

I looked up many places and find save as "StoreURL" but my library .dll doesn't support "StoreURL".


just a tip:

Write your program inside the AOO (or LibreOffice) by usage of the supported programming languages ant the API functions of the AOO (or LibreOffice)

You can use the built in StarBasic - if you want stick to a Basic like programming language. The AOO (and LibreOffice) has a built-in IDE for the macro programming.

Otherwise it is possible to achieve this task outside the AOO (or LO), but you must to control the relevant Application of the office suite and you must call the API functions of the office suite (like the StoreAsURL) anyway.
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9840
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Program AOO 4.0 with VB.NET

Postby JeJe » Mon Jun 08, 2020 10:54 am

Instead of using the dispatch helper its generally better to use OO Api calls.

Automating OpenOffice if you haven't seen this:

https://www.openoffice.org/udk/common/m ... ation.html
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1533
Joined: Wed Mar 09, 2016 2:40 pm

Re: Program AOO 4.0 with VB.NET

Postby Villeroy » Mon Jun 08, 2020 2:29 pm

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
Villeroy
Volunteer
 
Posts: 29707
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Program AOO 4.0 with VB.NET

Postby ChanHuynh » Mon Jun 08, 2020 4:21 pm

JeJe wrote:Instead of using the dispatch helper its generally better to use OO Api calls.

Automating OpenOffice if you haven't seen this:

https://www.openoffice.org/udk/common/m ... ation.html


Thank Jeje,
I had a look at this article, even it provides instruction to open and insert text, table to the document, it doesn't help me with the saving document. I also installed the newest version of OpenOffice 4.1.7 but I couldn't find anywhere I can reference the .dll library for my VB project. Do I need to import unoidl.com.sun.star to call OO API ? (I don't know how to use it)
OpenOffice 4.0
Windows 10
ChanHuynh
 
Posts: 7
Joined: Mon Jun 08, 2020 4:34 am

Re: Program AOO 4.0 with VB.NET

Postby ChanHuynh » Mon Jun 08, 2020 4:26 pm

Zizi64 wrote:
I looked up many places and find save as "StoreURL" but my library .dll doesn't support "StoreURL".


just a tip:

Write your program inside the AOO (or LibreOffice) by usage of the supported programming languages ant the API functions of the AOO (or LibreOffice)

You can use the built in StarBasic - if you want stick to a Basic like programming language. The AOO (and LibreOffice) has a built-in IDE for the macro programming.

Otherwise it is possible to achieve this task outside the AOO (or LO), but you must to control the relevant Application of the office suite and you must call the API functions of the office suite (like the StoreAsURL) anyway.


Thank Zizi,
is it a AOO macro ? I can work with macro just fine but want to develop an app that can open Calc Spreadsheet, do a little bit modification then save in PDF. The macro will require me to open the AOO first then it won't work in the server. :(
Im reading StarBasic, hopefully it will work.
OpenOffice 4.0
Windows 10
ChanHuynh
 
Posts: 7
Joined: Mon Jun 08, 2020 4:34 am

Re: Program AOO 4.0 with VB.NET

Postby ChanHuynh » Mon Jun 08, 2020 4:52 pm

Villeroy wrote:http://www.kalitech.fr/clients/doc/VB_APIOOo_en.html

Thank Villeroy,

I tried to follow the steps in there, it doesn't work at the line in bold below

Code: Select all   Expand viewCollapse view
Public Sub firstOOoProc()

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

        '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


Code: Select all   Expand viewCollapse view
oDoc = oDesk.loadComponentFromURL("private:factory/swriter", "_blank", 0, arg)
Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

I think the open office version he used back in 2006 should be OO 2.0 which maybe obsoleted :( .
My code works but I just can't save the spreadsheet. That is pretty weird
OpenOffice 4.0
Windows 10
ChanHuynh
 
Posts: 7
Joined: Mon Jun 08, 2020 4:34 am

Re: Program AOO 4.0 with VB.NET

Postby JeJe » Mon Jun 08, 2020 5:05 pm

try putting array() instead of arg, or declaring arg() as new com.sun.star.beans.PropertyValue
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1533
Joined: Wed Mar 09, 2016 2:40 pm

Re: Program AOO 4.0 with VB.NET

Postby JeJe » Mon Jun 08, 2020 5:12 pm

Never mind that... it doesn't work because you created a blank document then closed it before trying to store it.

Get rid of the first

oDoc.Close(True)
oDoc = Nothing
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1533
Joined: Wed Mar 09, 2016 2:40 pm

Re: Program AOO 4.0 with VB.NET

Postby ChanHuynh » Mon Jun 08, 2020 5:38 pm

JeJe wrote:Never mind that... it doesn't work because you created a blank document then closed it before trying to store it.

Get rid of the first

oDoc.Close(True)
oDoc = Nothing


It haven't get to the close document line yet. It just failed to open OpenOffice at this line
Code: Select all   Expand viewCollapse view
oDoc = oDesk.loadComponentFromURL("private:factory/swriter", "_blank", 0, arg)


My previous code (at the beginning of the topic) works until I tried to save it using
Code: Select all   Expand viewCollapse view
xDispatcher.executeDispatch(xComponent, ConvertFile(PDFFileName), "", "", ar)


I dont know why my library doesn't have option XStoreURL ? I can use XStoreURL in AOO Macro just fine
OpenOffice 4.0
Windows 10
ChanHuynh
 
Posts: 7
Joined: Mon Jun 08, 2020 4:34 am

Re: Program AOO 4.0 with VB.NET

Postby JeJe » Mon Jun 08, 2020 5:42 pm

You've got

oDoc.Close(True)
oDoc = Nothing

twice in that sub - once before you try and save it.
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1533
Joined: Wed Mar 09, 2016 2:40 pm

Re: Program AOO 4.0 with VB.NET

Postby JeJe » Mon Jun 08, 2020 5:49 pm

This line works for me from the OOBasic IDE
oDoc = oDesk.loadComponentFromURL("private:factory/swriter", "_blank", 0, arg)
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1533
Joined: Wed Mar 09, 2016 2:40 pm

Re: Program AOO 4.0 with VB.NET

Postby Zizi64 » Mon Jun 08, 2020 5:58 pm

but want to develop an app that can open Calc Spreadsheet, do a little bit modification then save in PDF. The macro will require me to open the AOO first


All of custom applications MUST open the AOO (or LO) Calc - visible or invisible - if you want to control the Calc application.
You can achieve this task inside a Teplate file too. Then you will able to launch your "conversion application" (the template file embedded the required macros) by a double click on the file name.
You can control the built-in file pickers to select a file to convert, or you can use constant file name or a directory name (if you want convert all of the files inside the directory...).
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9840
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Program AOO 4.0 with VB.NET

Postby ChanHuynh » Mon Jun 08, 2020 6:13 pm

JeJe wrote:This line works for me from the OOBasic IDE
oDoc = oDesk.loadComponentFromURL("private:factory/swriter", "_blank", 0, arg)


oh I saw that. It works fine in OOBasic IDE inside AOO. Could I compile code in OOBasic IDE to run like .exe file ?
writing in OOBasic IDE is better because I dont have to deal with library .dll issue ?

Thank Jeje
OpenOffice 4.0
Windows 10
ChanHuynh
 
Posts: 7
Joined: Mon Jun 08, 2020 4:34 am

Re: Program AOO 4.0 with VB.NET

Postby Zizi64 » Mon Jun 08, 2020 6:21 pm

Could I compile code in OOBasic IDE to run like .exe file ?


Not. (I do not know any method or procedure to achieve this task)

Use a template file with the embedded macro code.
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9840
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Program AOO 4.0 with VB.NET

Postby JeJe » Mon Jun 08, 2020 6:26 pm

Apache OpenOffice Basic is an interpreter language. Unlike C++ or Delphi, the Apache OpenOffice Basic compiler does not create executable or self-extracting files that are capable of running automatically. Instead, you execute an Apache OpenOffice Basic program inside Apache OpenOffice. The code is first checked for obvious errors and then executed line by line.


https://wiki.openoffice.org/wiki/Docume ... m_Overview

You can write an extension which is a portable way of installing code on other machines. That's just a case of creating a library, putting the code in it, then exporting the library as an extension.
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1533
Joined: Wed Mar 09, 2016 2:40 pm

Re: Program AOO 4.0 with VB.NET

Postby JeJe » Mon Jun 08, 2020 6:48 pm

If you look at an OO document's Tools menu/customise/events there's one called Open Document. You could use that event to run your macro when the document is opened.. and at the end of the code it closes the document... that would be sort of like an exe, apart from the document becoming visible for a bit.

Example Writer document just shows a message box and then closes

Edit: note once you've set it to close as soon as opened you won't be able to edit your macro.
Edit2: there would be a macro security issue though.
Attachments
Document does somethiing and closes itself .odt
(9.49 KiB) Downloaded 123 times
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1533
Joined: Wed Mar 09, 2016 2:40 pm

Re: Program AOO 4.0 with VB.NET

Postby ChanHuynh » Tue Jun 09, 2020 1:47 am

Thank Guys,
I managed to get the Document to save with executeDispatch. Below is my code

Code: Select all   Expand viewCollapse view
            Dim xContext As XComponentContext = Bootstrap.bootstrap()
            Dim xFactory As XMultiServiceFactory = DirectCast(xContext.getServiceManager(), XMultiServiceFactory)
            Dim xDesktop As unoidl.com.sun.star.frame.XDesktop = DirectCast(xFactory.createInstance("com.sun.star.frame.Desktop"), unoidl.com.sun.star.frame.XDesktop)
            Dim xComponentLoader As unoidl.com.sun.star.frame.XComponentLoader = DirectCast(xDesktop, unoidl.com.sun.star.frame.XComponentLoader)

            Dim arProps() As unoidl.com.sun.star.beans.PropertyValue = New unoidl.com.sun.star.beans.PropertyValue() {}

            'Open Document File
            Dim xComponent As unoidl.com.sun.star.lang.XComponent = xComponentLoader.loadComponentFromURL("private:factory/swriter", "_blank", 0, arProps)
            Dim xTextDocument As unoidl.com.sun.star.text.XTextDocument = DirectCast(xComponent, unoidl.com.sun.star.text.XTextDocument)
           
            'Adding new text
            Dim xText As unoidl.com.sun.star.text.XText = xTextDocument.getText()
            Dim xSimpleText As unoidl.com.sun.star.text.XSimpleText = DirectCast(xText, unoidl.com.sun.star.text.XSimpleText)
            Dim xCursor As unoidl.com.sun.star.text.XTextCursor = xSimpleText.createTextCursor()
           
            xText.insertString(xCursor, "Hello World, this is a new method of using Open Office " & vbCrLf, False)
     
            Dim args2() As unoidl.com.sun.star.beans.PropertyValue = New unoidl.com.sun.star.beans.PropertyValue(1) {}
            args2(0) = New unoidl.com.sun.star.beans.PropertyValue()
            args2(0).Name = "URL"
            args2(0).Value = New uno.Any("file:///C:/Users/Chan%20Huynh/Desktop/audit.odt")
            args2(1) = New unoidl.com.sun.star.beans.PropertyValue()
            args2(1).Name = "Filt).Value = New uno.Any("file:///C:/Users/Chan%20Huynh/Desktop/audit.odt")
            args2(1) = New unoidl.com.sun.star.beans.PropertyValue()
            args2(1).Name = "FilterName"
            args2(1).Value = New uno.Any("writer8")

            Dim xDispatcher As unoidl.com.sun.star.frame.XDispatchHelper = DirectCast(xFactory.createInstance("com.sun.star.frame.DispatchHelper"), unoidl.com.sun.star.frame.XDispatchHelper)

            xDispatcher.executeDispatch(xTextDocument.getCurrentController.getFrame, ".uno:SaveAs", "", 0, args2)

            xDesktop.terminate()erName"
            args2(1).Value = New uno.Any("writer8")

            Dim xDispatcher As unoidl.com.sun.star.frame.XDispatchHelper = DirectCast(xFactory.createInstance("com.sun.star.frame.DispatchHelper"), unoidl.com.sun.star.frame.XDispatchHelper)

            xDispatcher.executeDispatch(xTextDocument.getCurrentController.getFrame, ".uno:SaveAs", "", 0, args2)

            xDesktop.terminate()


For ThisComponent.CurrentController.Frame in OOBasic, I replaced by xTextDocument then GetCurrentController.GetFrame
Code: Select all   Expand viewCollapse view
xDispatcher.executeDispatch(xTextDocument.getCurrentController.getFrame, ".uno:SaveAs", "", 0, args2)


But I couldn't figure out what is equivalent to "ThisComponent" for Spreadsheet. Do you know which interface for Spreadsheet can be used ? I tried with unoidl.com.sun.star.sheet.XSpreadsheetDocument but it does not work.
OpenOffice 4.0
Windows 10
ChanHuynh
 
Posts: 7
Joined: Mon Jun 08, 2020 4:34 am

Re: Program AOO 4.0 with VB.NET

Postby JeJe » Tue Jun 09, 2020 9:35 am

ThisComponent is for all components. If the currently active component is a spreadsheet it will refer to that spreadsheet.

https://wiki.openoffice.org/wiki/Curren ... e_document

Use "private:factory/scalc" to create a new spreadsheet.
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1533
Joined: Wed Mar 09, 2016 2:40 pm


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 3 guests