Page 1 of 1
[Solved] Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 4:53 am
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 !
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 6:19 am
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.
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 10:54 am
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
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 2:29 pm
by Villeroy
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 4:21 pm
by ChanHuynh
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)
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 4:26 pm
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.
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 4:52 pm
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
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 5:05 pm
by JeJe
try putting array() instead of arg, or declaring arg() as new com.sun.star.beans.PropertyValue
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 5:12 pm
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
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 5:38 pm
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
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 5:42 pm
by JeJe
You've got
oDoc.Close(True)
oDoc = Nothing
twice in that sub - once before you try and save it.
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 5:49 pm
by JeJe
This line works for me from the OOBasic IDE
oDoc = oDesk.loadComponentFromURL("private:factory/swriter", "_blank", 0, arg)
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 5:58 pm
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...).
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 6:13 pm
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
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 6:21 pm
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.
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 6:26 pm
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.
Re: Program AOO 4.0 with VB.NET
Posted: Mon Jun 08, 2020 6:48 pm
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.
Re: Program AOO 4.0 with VB.NET
Posted: Tue Jun 09, 2020 1:47 am
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.
Re: Program AOO 4.0 with VB.NET
Posted: Tue Jun 09, 2020 9:35 am
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.