VFP Automates OpenOffice.org
By Ted Roche
OpenOffice.org and StarOffice are office productivity packages
your clients may be considering. When they come to you to ask if you
can support their move with your Visual FoxPro application, you can
say, "Yes, FoxPro plays well with others." However, Ted Roche is here
to explain that all is not yet rosy. Read on to understand the state of
the art in automating these packages.
For a variety of reasons, computer users (our clients) have
started looking at alternative office software. Some are concerned
about rising licensing costs and liabilities, others about security,
trustworthiness, or commercial viability. The good news is that we, as
FoxPro developers, can continue to offer these clients the power of
Visual FoxPro, even if they choose other office suites. FoxPro supports
interoperability with a wide variety of other languages, applications,
and platforms. In this article, I'll look at VFP interoperability with
A little background history provides some perspective on how
things got to be the way they are. OpenOffice.org started out life as
StarOffice, developed by Star Division, Inc., in Germany. StarOffice
was bought by Sun Microsystems, Inc., which continues to enhance and
sell it as a commercial package (StarOffice) while also offering a
freely distributable version as OpenOffice.org. The two products are
available under two different licenses: StarOffice under the Sun
Industry Standards Source License, for commercial entities to
re-license, embed, or extend, and OpenOffice.org under the Lesser GNU
General Public License (LGPL). Sun Microsystems, Inc., retains the
copyright to all code in the project. StarOffice has a very reasonable
retail MSRP of $75.95, with quantity discounts as low as $25 per
package for 10,000 users. Larger companies will appreciate the more
complete StarOffice package (fonts, templates) and support from Sun,
while smaller organizations will appreciate the free price and open
community support of the millions of OpenOffice.org users. This type of
dual-licensing scheme is becoming more common: MySQL AB has commercial
and open-source licenses, as do Mozilla and Perl. For simplicity, in
the remainder of this article, I'll refer to OpenOffice.org, but the
same comments should apply to StarOffice as well.
OpenOffice.org has the usual products you'd expect in an
office suite: a word processor ("Writer"), spreadsheet ("Calc"),
presentation package ("Impress"), structured drawings ("Draw"), and an
equations editor ("Math"). OpenOffice.org reads a number of document
formats, including those of Microsoft Office, as well as most industry
standards: text, HTML, RTF, and previous OpenOffice.org and StarOffice
formats. As OpenOffice.org supports a different (StarBasic) macro
language and object model, Microsoft Office document macros aren't
translated to the new format, and must be converted manually.
OpenOffice.org's native document format is a compressed XML format.
Unlike other proprietary office document packages, this format is
openly documented. Open any document with a ZIP viewer, such as WinZIP,
and you'll see a set of XML documents. These documents can be
disassembled and reassembled to create new documents.
So, the good news is that your basic documents and templates
will be readable without major changes. However, if you're moving over
an automated system, you'll need to recode the Automation portions, as
the language and object model is significantly different. For this
reason, you'll probably want to start out by automating a new system,
rather than converting an existing one.
StarBasic is a variant of the BASIC language with
built-in objects for the OpenOffice.org environment. Listing 1 shows a simple example of a StarBasic
macro. Macros can be created from the Tools | Macro menu item. As of
the current version (1.0.2), there isn't a macro recorder built into
the product—that's high on the priority list for a future version
(there is a macro editor, with an IDE). StarBasic supports declared and
undeclared variables (arrays must be DIMmed), one- and two-dimensional
arrays, underscore for line continuation, and ampersands or plus signs
for string concatenation. If you've worked with any variant of BASIC
before, you should be able to pick this language up pretty quickly.
Listing 1. StarBasic macro to convert temperatures.
'Accept Fahrenheit temperature, return Celsius
dim lcInput as String
dim lnFahrenheit as double
dim lnOutput as double
lcInput = inputbox("Enter a temperature " +_
"Fahrenheit to Celsius "+_
lnFahrenheit = cDbl(lcInput)
lnCelsius = (lnFahrenheit -32) * 5/9
MsgBox("That is " + Cstr(lnCelsius) + _
" degrees Celsius", 0 + 64 + 0, _
"Temperature Conversion Result")
OpenOffice.org has a different object model than other
applications you may be familiar with. The OpenOffice.org development
team examined the various industry standard formats (Microsoft's COM,
Sun's RMI, CORBA), and found them all lacking in one aspect or another.
Since they were interested from the beginning in creating a
cross-platform application, they chose to create a superset of all of
the office formats. The result is Universal Network Objects, their own
object management system. The next step was to build "bridges" from
each of the languages for invocation of the UNO objects from other
systems: C++, Java, and COM-based languages. The UDK (development kit),
currently in alpha version, has examples of creating OpenOffice.org
scripts in VBScript and Java. A sample is shown as Listing 2. A more extensive demo from the
OpenOffice.org UDK is included in the Download
file as WriterDemo.vbs.
Listing 2. VBScript for manipulating OpenOffice.org Writer.
'The service manager is always the starting point
'If there is no office running then an office is started
Set objServiceManager= Wscript._
'Create the DesktopSet
'Open a new empty writer document
"_blank", 0, args)
'Create a text object
Set objText= objDocument.getText
'Create a cursor object
Set objCursor= objText.createTextCursor
'Inserting some Text
"The first line in the created text document."&_
Visual FoxPro developers should be pretty familiar with
converting code from other languages, and this one presents only one
tough problem. While most of the conversion is straightforward, the
line "Dim args()" is the killer. This creates an array in VBScript, one
with no elements. VFP doesn't have the concept of a completely empty
(row count of zero) array. If you try to pass the args array to the
next function, you'll get OLE error 0x80020005: Type mismatch. A little
digging in the documentation will reveal the args array should be
passing a series of PropertyValue objects to the function—settings that
specify how the document should be created. Since we can't pass an
empty array, we need to create a PropertyValue object and pass it in.
How do you create a PropertyValue object? It's not a native VFP class,
so we need to ask OpenOffice.org to do it for us using a technique
called "reflection." Pass in a reference to a VFP object, and
OpenOffice.org returns the specified object in its place. The resulting
code, in Listing 3, shows
Visual FoxPro invoking the OpenOffice.org Writer, and adding a bit of
text to a new document.
Listing 3. The resulting VFP code to accomplish the same as
* OpenOffice.org automation
* Sample from http://udk.openoffice.org/common/
* Translated to VFP code
* Ted Roche, 30 Dec 2001
LOCAL loOfcMgr, loDesktop, loDocument, args(1), ;
loCoreReflection, loPropertyValue, loText,;
* Create the Service Manger and Desktop
loOfcMgr = CreateObject(;
loDesktop = loOfcMgr.createInstance(;
* The args array is an array of "PropertyValue"
* objects - create by invoking OO.o reflection
loCoreReflection = loOfcMgr.createInstance( ;
loPropertyValue = CREATEOBJECT("Empty")
args = loPropertyValue
args.name = "ReadOnly"
args.value = .F.
* Tell VFP to pass arrays to the loDesktop object
* as zero-based, by reference
* Open a new empty writer document
loDocument = loDesktop.loadComponentFromURL(;
"private:factory/swriter","_blank", 0, @args)
* Insert sample Text
loText=loDocument.getText() && Create text object
* Create a cursor object (position pointer)
"The first line in the created text document."+;
CHR(10) + "Fox Rocks!", .F.)
For those of us who are used to COM Automation, working with
OpenOffice.org has a ways to go to provide the same ease of use. There
are no type libraries to load into the Object Browser, nor will
IntelliSense let you explore the object model interactively. At this
point, only the written and Web-based documentation is available, so a
fair amount of trial and error is required to develop automated
solutions in OpenOffice.org.
OpenOffice.org is a full-fledged office package with
most (if not all) of the functionality your clients need for word
processing, spreadsheets, and presentations. OpenOffice.org offers your
clients the freedom to choose an office package that meets their
support needs and budget. The developer's kit is in beta stage, so
digging through the documentation, and making sense of it all, is the
biggest challenge for us developers at this point. I encourage you to
download, install, and evaluate the package and the SDK (it's all
free!) for your clients who will be asking you about it. Test out the
macro capability, work with the Automation interface, and consider
whether you should be proposing OpenOffice.org for the next client
needing office document Automation.
Download your copy of OpenOffice.org from the links at
Sun Microsystems, Inc. StarOffice home page is at www.staroffice.com. The UNO
Development Kit can be found at http://udk.openoffice.org.
In-depth explanations of how the OLE Automation bridge is constructed
can be found at http://udk.openoffice.org/common/man/spec/ole_bridge.html.
A short form of the history of StarOffice and OpenOffice.org is at www.openoffice.org/about_us/milestones.html.
An interesting white paper—subtitled "Why is Sun Doing this?"—can be
found at www.openoffice.org/white_papers/OOo_project/OOo_project.html.
The office Automation tutorial, on which the example is based, is
located at http://udk.openoffice.org/common/man/tutorial/office_automation.html.
Finally, thanks to Carlos Guzmán Alvarez, who provided the
solution to the empty args() array problem in the December 2002 issue
of FoxPress at www.fpress.com/revista/Num1202/Truco.htm.