[Solved] Program AOO 4.0 with VB.NET

[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 !

[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.

[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

[Villeroy]

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

[ChanHuynh]

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)

[ChanHuynh]

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.

[ChanHuynh]

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

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

[JeJe]

try putting array() instead of arg, or declaring arg() as new com.sun.star.beans.PropertyValue

[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

[ChanHuynh]

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

[JeJe]

You've got

oDoc.Close(True)
oDoc = Nothing

twice in that sub - once before you try and save it.

[JeJe]

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

[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...).

[ChanHuynh]

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

[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.

[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.

[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.

[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.

[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.