Version 0.8, May-06-2006
French translation is here, but it may be outdated, please check version number and date.
Public Documentation License Notice The contents of this Documentation are subject to the Public Documentation License Version 1.0 (the "License"); you may only use this Documentation if you comply with the terms of this License. A copy of the License is available at http://www.openoffice.org/licenses/PDL.html. The Original Documentation is "Programmer OpenOffice.org avec Visual Basic". The Initial Writer of the Original Documentation is Didier ALAIN Copyright (C)2004-2005. All Rights Reserved. (Initial Writer contact(s): email@example.com). Contributor(s): Bernard Marcelly, Laurent Godard, Christophe Thibierge. Portions created by ______ are Copyright (C)_________[Insert year(s)]. All Rights Reserved. (Contributor contact(s):________________[Insert hyperlink/alias]).
This document is a work in progress : it may still contain some mistakes, misunderstandings or imprecisions
This document is based on work of KaliTech SARL (http://www.kalitech.fr), french editor of KaliSuite, quality management software.
The reader is supposed to have a minimal knowledge about Visual Basic programming concepts and practise.
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 Set oSM = CreateObject("com.sun.star.ServiceManager") 'Create the first and most important service Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop") 'Create a new doc Set oDoc = oDesk.loadComponentFromURL("private:factory/swriter", "_blank", 0, arg()) 'Close the doc oDoc.Close (True) Set oDoc = Nothing 'Open an existing doc (pay attention to the syntax for first argument) Set 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) Set oDoc = Nothing End Sub
The Automation technology can only be used with OpenOffice.org on a Windows platform (Windows 95, 98, NT4, ME, 2000, XP). There are COM implementations on Macintosh OS and UNIX, but there has been no effort to support Automation on these platforms.
Using Automation involves creating objects in a COM-like fashion, that is, using functions like CreateObject() in VB or CoCreateInstance() in C. This requires the OpenOffice.org automation objects to be registered with the Windows system registry. This registration is carried out whenever an office is installed on the system. If the registration did not take place, for example because the binaries were just copied to a certain location, then Automation clients will not work correctly or not at all. Refer to 3.4.4 Professional UNO - UNO Language Bindings - Automation Bridge - The Service Manager Component for additional information."Useful links section below).
Servicesgroup interfaces, methods and properties which permit the manipulation of objects. Thus, to make things clear, we could say that object = service !
serviceprovides a set of
interfacesexport object methods and properties. But with Basic languages (OOo Basic as well as Visual Basic), interfaces are not invokated, because they are hidden for easyness. Unfortunately, that easyness becomes a difficulty when trying to understand API documentation, because
interfaceis a central concept in it ! The same
interfaceis common to many services : that's the of an Object-oriented API. As a consequence, when one is able to manipulate an
service, one is able to manipulate that
Services, representing objects, have properties. Properties are defined by the (value, name) pair. Some properties are of
structuredata type, which is a problem in Visual Basic (see below).
Interfacesexport a set of
methods, which in turn give acess to object properties.
enumerationsand data structures.
What is very specific to the object-oriented programming world is the (modules-)services||interfaces-method layer.
Interfaces may be identical for many services to which they give access (see this topic in OOoforum, from DannyB). What makes an interface is that it always exports the same methods, whatever object it gives access to. But due to inheritance mechanism, it is often very difficult to find which methods is provided for one service : a service -> multiple interfaces -> multiple methods.
Whatever you do, you do it accross interfaces, bound with their services. Thus :
interfaces(hidden in BASIC)
Historically, the first versions of StarOffice (up to version 5.2) used to launch the office in a specific desktop environment (graphical environments as KDE or gnome were not mature enough and lightweight X Window managers were widely used). After that, the user used to launch an office component, even in MS Windows (very heavy...). StarOffice integration in OSes GUI desktops has much evolved, but the Desktop object is still very important in OOo API. It offers by itself many services, thus independently of Office components.
This leads to a distinction between context-dependent services
(services included in office components), and context-independent services,
callable via the global
It is possible to program OOo API with different languages : C++, java, python, StarBasic, ECMAScript. On the other way, you can also make use of the COM technology from Microsoft : *.NET (still in development), Visual C++, Visual Basic, VBScript, thanks to the UNO-Automation bridge. OOo API is abstract and independent from programming languages. Thus, one has to read and "translate" the API to the programming language : that's the main difficulty !
set oRootOOoObject = CreateObject(...)^VB command
set oOOoObject = oRootOOoObject.createInstance(...)^OOo API method : everything after this point is from the API
services. The main reason is that one always accesses and manipulates objects through services, and never in a direct way (services is a abstraction layer between objects and programming languages). Additionnaly, API objects often represents abstract functions, that's the reason why service is a correct term.
com.sun.star.lang.XComponentinterface. So when you see the
componentterm in the API documentation, it refers to a document. Component also refers to an Office Application (Writer, Calc and so on).
structure. Structures are the equivalent of User-Defined Type (UDT) in Visual Basic. Because of the implementation of the UNO-Automation bridge, it is not possible to pass an UDT as a method argument. (cf http://api.openoffice.org/docs/DevelopersGuide/ProfUNO/ProfUNO.htm#1+4+4+5+3+Usage+of+Types). From Visual Basic, one has to use a particular method of OOo API (Bridge_GetStruct) : it gives access to an OOo structure from Visual Basic. After that, one can give the result of the Bridge_GetStruct call as argument in a method call.
Function MakePropertyValue(cName, uValue) As Object Dim oPropertyValue As Object Dim oSM As Object Set oSM = CreateObject("com.sun.star.ServiceManager") Set oPropertyValue = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue") oPropertyValue.Name = cName oPropertyValue.Value = uValue Set MakePropertyValue = oPropertyValue End FunctionYou could use this function in a portion of code as below. For this example, we use the
loadComponentFromUrlmethod. This method accepts as fourth argument a structure that permits opening parameters.
Sub openDoc() Rem Rem Load an existing writer document, with opening parameters Rem Dim oSM, ODesk as Object 'root object from OOo API Dim oDoc as Object 'The document to be opened Dim OpenPar(2) As Object 'a Visual Basic array, with 3 elements 'Instanciate OOo : the first line is always required from Visual Basic for OOo Set oSM = CreateObject("com.sun.star.ServiceManager") Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop") 'We call the MakePropertyValue function, defined just before, to access the structure Set OpenPar(0) = MakePropertyValue("ReadOnly", True) Set OpenPar(1) = MakePropertyValue("Password", "secret") Set OpenPar(2) = MakePropertyValue("Hidden", False) 'Now we can call the OOo loadComponentFromURL method, giving it as 'fourth argument the result of our precedent MakePropertyValue call Set oDoc = oDesk.loadComponentFromURL("file:///c|test.sxw", "_blank", 0, OpenPar) ... End Sub
Call oDoc.Print...The VB
CallByName oDoc, "print", vbMethod, arg 'arg is an array of arguments (cf MakePropertyValue)
replace- also usefull - function !). Sometimes, it is difficult to know if you call an OOo Basic function or an OOo API function, but you can always verify it with the OOo Basic Online Help, in the 'other commands' section. Don't get fooled by this. Here is a non-exhaustive list :
Const com_sun_star_text_ControlCharacter_PARAGRAPH_BREAK = 0to be written : an exhaustive constant declaration list
' 'Converts a Ms Windows local pathname in URL (RFC 1738) 'Todo : UNC pathnames, more character conversions ' Public Function ConvertToUrl(strFile) As String strFile = Replace(strFile, "\", "/") strFile = Replace(strFile, ":", "|") strFile = Replace(strFile, " ", "%20") strFile = "file:///" + strFile ConvertToUrl = strFile End Function
' 'Creates a sequence of com.sun.star.beans.PropertyValue s ' Public Function MakePropertyValue(cName, uValue) As Object Dim oStruct, oServiceManager as Object Set oServiceManager = CreateObject("com.sun.star.ServiceManager") Set oStruct = oServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue") oStruct.Name = cName oStruct.Value = uValue Set MakePropertyValue = oStruct End Function
' 'A simple shortcut to create a service ' Public Function CreateUnoService(strServiceName) As Object Dim oServiceManager As Object Set oServiceManager = CreateObject("com.sun.star.ServiceManager") Set CreateUnoService = oServiceManager.createInstance(strServiceName) End Function
Public Function FileName2URL(ByVal pFileName As String, _ Optional ByVal pConvertBackslashesToSlashes As Boolean = True) _ As String Dim s As String Dim z As String Dim j As Long Dim x As Integer On Error Resume Next s = "" For j = 1 To Len(pFileName) z = Mid(pFileName, j, 1) x = Asc(z) Select Case x Case 9 z = "%09" Case 13 z = "%0d" Case 10 z = "%0a" 'Case 32 ' z = "+" Case 32 To 35, 37 To 41, 43, 44, 59 To 63, 91, 93, 94, 96, Is >= 123 z = "%" & Hex(x) Case 92 If pConvertBackslashesToSlashes Then z = "/" End Select s = s & z Next s = "file:///" & s FileName2URL = s End Function
Sub bk_demo() ' ' Bookmark demo ' ' You need an existing writer doc (c:\tmp\testdoc.odt), ' execute in step by step mode to follow ' result in the doc (use Writer navigator to see bookmarks) ' ' Note : this sample code works with writer documents (sxw or odt formats) ' as well as with MsOffice Word documents ' Dim oSM, oDesk, oDoc, oTxt, oBk, oRng As Object 'OOo objects Dim strBkName As String Dim arg() Set oSM = CreateObject("com.sun.star.ServiceManager") Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop") Set oDoc = oDesk.loadComponentFromURL("file:///C:/tmp/testdoc.odt", "_blank", 0, arg()) Set oTxt = oDoc.Text 'Inserts a bookmark at the beginning of the text Set oBk = oDoc.createInstance("com.sun.star.text.Bookmark") oBk.Name = "New_Bookmark_Beginning" Set oRng = oTxt.getStart() Call oTxt.insertTextContent(oRng, oBk, True) 'Inserts a bookmark at the end of the text Set oBk = oDoc.createInstance("com.sun.star.text.Bookmark") oBk.Name = "New_Bookmark_End" Set oRng = oTxt.getEnd() Call oTxt.insertTextContent(oRng, oBk, True) 'Inserts some text at an existing bookmark, requested by name strBkName = "New_Bookmark_End" oDoc.getBookmarks().getByName("New_Bookmark_End").getAnchor.setString ("**Inserted at '" & strBkName & "' by VBA code** ") 'Deletes an existing bookmark, requested by name strBkName = "New_Bookmark_End" Set oBk = oDoc.getBookmarks().getByName(strBkName) Set oTxt = oDoc.Text Call oTxt.removeTextContent(oBk) 'Cleaning all objects Call oDoc.Close(False) 'Close without saving Set oBk = Nothing Set oRng = Nothing Set oTxt = Nothing Set oDoc = Nothing Set oDesk = Nothing Set oSM = Nothing End Sub
Sub SaveAsPDF_demo() ' ' Save an existing writer document as PDF ' Dim oSM, oDesk, oDoc As Object 'OOo objects Dim OpenParam(1) As Object 'Parameters to open the doc Dim SaveParam(1) As Object 'Parameters to save the doc Set oSM = CreateObject("com.sun.star.ServiceManager") Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop") Set OpenParam(0) = MakePropertyValue("Hidden", True) ' Open the file hidden Set oDoc = oDesk.loadComponentFromURL("file:///C:/tmp/testdoc.odt", "_blank", 0, OpenParam()) Set SaveParam(0) = MakePropertyValue("FilterName", "writer_pdf_Export") Call oDoc.storeToURL("file:///C:/tmp/testdoc.pdf", SaveParam()) Set oDesk = Nothing Set oSM = Nothing End Sub
Private Sub Search_Replace() 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 ! Dim mmerge As Object 'Instanciate OOo : this line is mandatory with VB for OOo API Set oSM = CreateObject("com.sun.star.ServiceManager") 'Create the first and most important service Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop") 'Create a new doc Set oDoc = oDesk.loadComponentFromURL("file:///c:/temp/oo2.sxw", "_blank", 0, arg()) ' jon code Dim objText As Object, objCursor As Object Set objText = oDoc.GetText Set objCursor = objText.createTextCursor ' replace all Dim oSrch As Object 'Set oSrch = oDoc.createSearchDescriptor Set oSrch = oDoc.createReplaceDescriptor oSrch.setSearchString ("[name]") oSrch.setReplaceString ("Smith, Robert James") Debug.Print oDoc.replaceAll(oSrch) oSrch.setSearchString ("[city]") oSrch.setReplaceString ("West Palm Beach") Debug.Print oDoc.replaceAll(oSrch) oSrch.setSearchString ("[state]") oSrch.setReplaceString ("Florida") Debug.Print oDoc.replaceAll(oSrch) oSrch.setSearchString ("[zip]") oSrch.setReplaceString ("33411") Debug.Print oDoc.replaceAll(oSrch) Dim arg1(0) As Object Set arg1(0) = MakePropertyValue("", 0) CallByName oDoc, "print", VbMethod, arg1 'arg is an array of arguments (cf MakePropertyValue) 'Close the doc oDoc.Close (True) Set oDoc = Nothing End Sub