[Solved] Program AOO 4.0 with VB.NET

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
ChanHuynh
Posts: 7
Joined: Mon Jun 08, 2020 4:34 am

[Solved] Program AOO 4.0 with VB.NET

Post by ChanHuynh »

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

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

 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 detail
Reference detail
Last edited by ChanHuynh on Wed Jun 10, 2020 3:10 am, edited 2 times in total.
OpenOffice 4.0
Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Program AOO 4.0 with VB.NET

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Program AOO 4.0 with VB.NET

Post by JeJe »

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
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Program AOO 4.0 with VB.NET

Post by Villeroy »

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
ChanHuynh
Posts: 7
Joined: Mon Jun 08, 2020 4:34 am

Re: Program AOO 4.0 with VB.NET

Post by ChanHuynh »

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

Post by ChanHuynh »

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

Post by ChanHuynh »

Thank Villeroy,

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

Code: Select all

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

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
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Program AOO 4.0 with VB.NET

Post by JeJe »

try putting array() instead of arg, or declaring arg() as new com.sun.star.beans.PropertyValue
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Program AOO 4.0 with VB.NET

Post by JeJe »

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
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
ChanHuynh
Posts: 7
Joined: Mon Jun 08, 2020 4:34 am

Re: Program AOO 4.0 with VB.NET

Post by ChanHuynh »

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

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

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
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Program AOO 4.0 with VB.NET

Post by JeJe »

You've got

oDoc.Close(True)
oDoc = Nothing

twice in that sub - once before you try and save it.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Program AOO 4.0 with VB.NET

Post by JeJe »

This line works for me from the OOBasic IDE
oDoc = oDesk.loadComponentFromURL("private:factory/swriter", "_blank", 0, arg)
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Program AOO 4.0 with VB.NET

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
ChanHuynh
Posts: 7
Joined: Mon Jun 08, 2020 4:34 am

Re: Program AOO 4.0 with VB.NET

Post by ChanHuynh »

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
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Program AOO 4.0 with VB.NET

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Program AOO 4.0 with VB.NET

Post by JeJe »

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.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Program AOO 4.0 with VB.NET

Post by JeJe »

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 348 times
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
ChanHuynh
Posts: 7
Joined: Mon Jun 08, 2020 4:34 am

Re: Program AOO 4.0 with VB.NET

Post by ChanHuynh »

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

Code: Select all

            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

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
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Program AOO 4.0 with VB.NET

Post by JeJe »

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.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Post Reply