Controlling Microsoft Office from your app

Office Automation consists of a module and classes that are used to access the Microsoft Office Automation Object model for controlling Word, Excel and PowerPoint. You will need to copy the MSOfficeAutomation plugin (located in the Extras folder of the installation) to the Plugins folder before you can use these classes.

Note

Office Automation only works on Microsoft Windows.

Module and class overview

Office module

This module contains all the enums you'd need for Office automation.

  • Excel constants begin with xl

  • PowerPoint constants begin with pp

  • Word constants begin with wd

  • General Office constants begin with mso

The classes below give you access to the Microsoft Office Automation Object model for controlling Word, Excel and PowerPoint. Refer to Microsoft's official documentation to learn about the Office Automation Object model:

ExcelApplication class

The ExcelApplication class inherits from OLEObject and is used to automate Excel.

PowerPointApplication class

The PowerPointApplication class inherits from OLEObject and is used to automate PowerPoint.

WordApplication class

The WordApplication class inherits from OLEObject and is used to automate Word.

Office automation under Xojo versus Visual Basic for Applications (VBA)

In most cases it ought to be fairly straightforward to port VBA Office Automation code to Xojo, but there are differences. Below are some ways that Xojo differs from VBA:

  • No implied Application instance

  • Xojo prefixes all Excel, PowerPoint and Word classes

  • Office constants live inside Office Module

  • Collection objects cannot be iterated using For Each loop

  • No named parameter support

  • Some keywords conflict with method names

  • Xojo provides better exception handling

Working from the Application class in VBA

There is an implied Application instance when you write VBA code from within Excel, PowerPoint, or Word. For Example:

' This is VBA code
Var pres as Presentation
Var slide1 as Slide

Set pres = Presentations.Add
' The above is the same as saying:
' Set pres = Application.Presentations.Add

Set slide1 = pres.Slides.Add(1, ppLayoutText)

If you are in PowerPoint itself, then the above code would run just fine since it knows what a Presentation object is. Obviously if you typed this code in either Word or Excel, it would generate errors. So what does this code look like in Xojo? Here's the Xojo code:

Var powerPoint As New PowerPointApplication
Var pres As PowerPointPresentation
Var slide1 As PowerPointSlide

pres = powerPoint.Presentations.Add
slide1 = pres.Slides.Add(1, Office.ppLayoutText)

Using Collection objects

Xojo does not understand VB/OLE collection objects, therefore you cannot use the For Each...Next statement to iterate through the collection. This just means you need to use a counter variable in your For loop. Here is a VBA example:

' VBA code
For Each doc In Documents
  doc.Range.Text = "Hello world!"
Next

This is the above code translated to Xojo:

' Xojo code
For i As Integer = 1 To Word.Documents.Count
  Word.Documents(i).Range.Text = "Hello world!"
Next

Passing parameters by name

Xojo doesn't support passing parameters by name, however you can still achieve the equivalent behavior. First of all, you have to understand how to use the OLEObject. You can read up on the docs, but for brevity here's a quick example you can use as a template.

Here's a find and replace macro in Word VBA:

' This is VBA code
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
  .Text = "find this"
  .Replacement.Text = "replace with"
  .Wrap = wdFindContinue
  .Format = false
  .MatchCase = false
  .MatchWholeWord = false
  .MatchWildcards = false
  .MatchSoundsLike = false
  .MatchAllWordForms = false
End With
Selection.Find.Execute Replace:=wdReplaceAll

Here's what the above code looks like in Xojo:

Var word As New WordApplication
Var find As WordFind

' Setting the properties is largely the same
find = word.Selection.Find
find.ClearFormatting
find.Replacement.ClearFormatting
find.text = "find this"
find.Replacement.Text = "replace with"
find.Wrap = Office.wdFindContinue
find.Format = False
find.MatchCase = False
find.MatchWholeWord = False
find.MatchWildcards = False
find.MatchSoundsLike = False
find.MatchAllWordForms = False

' To execute the Find, you have to create an OLEParameter
' and then set its position based on the MS Office model docs.
Var replaceParam As New OLEParameter
replaceParam.Value = Office.wdReplaceAll
' According to the docs on Find.Execute the Replace parameter is the 11th
replaceParam.Position = 11
find.Execute(replaceParam)

That's all there is to it. Obviously, finding the correct position of that named parameter is the trickiest part. But it's about the only time when you really need to launch VBA and look it up in its Object Browser.

Conflicting keywords

There are certain reserved keywords in Xojo (these will usually be hilighted in a different color, such as'select' or 'End') that cannot be used as method names or property names. Unfortunately, Excel, as an example, uses some of these names for its methods/properties. To get around this problem, you can suffix the method/property name that you want access to with an underscore character. Here's an example of how you'd call the "Select" method in Excel from Xojo:

Excel.Range("A1", "A3").Select_

Since the keyword'select' is reserved, you suffix it with an underscore character and Xojo will hand it off to Excel as "Select".

Usage

This simple example creates a new Word document and adds some text to it from a TextArea on the Window called SampleTextArea:

Try
  ' Creates a new Word document and
  ' adds the text from SampleTextArea to the
  ' document
  Var doc As WordDocument
  Var style As WordStyle

  Var wordApp As New WordApplication
  wordApp.Visible = True
  doc = wordApp.Documents.Add
  doc.Range.Text = SampleTextArea.Value
Catch err As OLEException
  MessageBox(err.Message)
End Try

Troubleshooting

How can you tell if Windows has the required OLE libraries installed?

One easy way is to load up Visual Basic Editor (under the Tools and Macros menu), and do some automation with VBA. Here are the steps you can use to automate PowerPoint from Word: 1. Start Word.

  1. Start Visual Basic Editor in Word.

  2. Insert a UserForm.

  3. Add a CommandButton to the form.

  4. In the click event of the button, put in this code:

Var obj As Object
Set obj = CreateObject("PowerPoint.Application")
obj.Activate

  1. Run the program and click on the button.

  2. If PowerPoint loads up and you don't get any errors, then the OLE libraries are installed.

What are the possible errors that can be caught?

Errors come through OLE, so you need to catch OLEException. This will report the last command that failed along with any additional information about the exception. You can use either the Exception or Try...Catch commands to catch exceptions.

With Exception:

Try
  Var word As New WordApplication
  word.ShowClipboard
Catch err As OLEException
  MessageBox(err.Message)
End Try

With Try...Catch:

Try
  Var word As New WordApplication
  word.ShowClipboard
Catch e As OLEException
  MessageBox(e.Message)
End Try

More information