Communicating with Microsoft Office Automation in Xojo

Microsoft Office for Windows can be controlled using something called the VBA Office Object Model. You can use Xojo to communicate with this object model so that you can control Word, Excel and PowerPoint from your Xojo apps on Windows.

To use Office Automation with Xojo, you have to first ensure that the Office Automation plugin is in the Xojo Plugin folder before you start Xojo. You can find the plugin in the Extras/Office Automation folder of your Xojo installation. Simply copy it to the main Plugins folder in the Xojo installation folder.

After doing this, you’ll find that there is a new module and many new classes that are available to you. In particular, you can now use these:

Office module

ExcelApplication class

PowerPointApplication class

WordApplication class

You can use these to communicate with the Office Object model so that you can control Excel, PowerPoint and Word.

Note: Office Automation only works on Microsoft Windows.

Here a quick example to show you how you can send text to Microsoft Word.

Create a new Xojo Desktop project. Drag the WordApplication control from the Library onto the Window1 layout. This will put the control into the Shelf at the bottom of the Layout Editor. Change its name to WordApp. Add a TextArea to the layout. Change its name to WordArea. Add a Button to the layout. Add the Action event handle to the Button and add this code: Dim doc As WordDocument WordApp.Visible = True doc = WordApp.Documents.Add doc.Range.Text = WordArea.Text Exception err As OleException MsgBox(err.Message) Run the project, enter some text in the TextArea and click the button. Microsoft Word will start (if it’s not already running) and a new document will be created with your text added.

The above code uses the WordDocument class, which maps to the Office Object Model’s Document class. In that class, it uses the Document class’s Range method to set the text for the document.

You can refer to the official Microsoft docs for specifics on the Office Object Model:

For other tips on how to use Office Automation with Xojo, refer to the Office Automation Overview page in the Xojo Dev Center.



