Office Automation Overview
From Xojo Documentation
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.
|Office Automation only works on Microsoft Windows.|
- 1 Module and Class Overview
- 2 Office Automation under Xojo versus Visual Basic for Applications (VBA)
- 3 Usage
- 4 Trouble Shooting
- 5 More Information
Module and Class Overview
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:
The ExcelApplication class inherits from OLEObject and is used to automate Excel.
The PowerPointApplication class inherits from OLEObject and is used to automate PowerPoint.
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 Dim pres as Presentation Dim 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 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:
For i As Integer = 1 To Word.Documents.Count
Word.Documents(i).Range.Text = "Hello world!"
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 find As WordFind
// Setting the properties is largely the same
find = word.Selection.Find
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
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.
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:
Since the keyword 'Select' is reserved, you suffix it with an underscore character and Xojo will hand it off to Excel as "Select".
// adds the text from SampleTextArea to the
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
Exception err As OLEException
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:
- Start Word.
- Start Visual Basic Editor in Word.
- Insert a UserForm.
- Add a CommandButton to the form.
- In the click event of the button, put in this code:
- Run the program and click on the button.
- 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.
Exception err As OLEException
Catch e As OLEException
- Office Automation video
- These example projects are included with Xojo:
- Platform-Specific/Windows/Office Automation/Excel Automation
- Platform-Specific/Windows/Office Automation/PowerPoint Automation
- Platform-Specific/Windows/Office Automation/Word Automation
- These 3rd party books are available: