Class # Office --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ## Description The class that contains the enums you need for `Controlling Microsoft Office from your app`. ## Notes Office Automation is supported only under Windows. The language that you use to automate Microsoft Office applications is documented by Microsoft and numerous third-party books on Visual Basic for Applications (VBA). Microsoft Office applications provide online help for VBA. In Office 2007, click the Microsoft Office button and then click Options. Then select Popular and select the Show Developer Tab in the Ribbon checkbox. You will then see a Visual Basic button in the Code group in the ribbon and menubar now includes a Help menu which leads to the VB online help. To access the online help in Office 2003, choose Macros from the Tools Menu of your MS Office application, and then choose Visual Basic Editor from the Macros submenu. When the Visual Basic editor appears, choose Microsoft Visual Basic Help from the Help menu. The help is contextual in the sense that it provides information on automating the Office application from which you launched the Visual Basic editor. If VBA Help does not appear, you will need to install the help files. Windows Office 2003 prompts you to install the VBA help files when you first request VBA help. You don't need the master CD. Microsoft has additional information on VBA at and have published their own language references on VBA. One of several third-party books on VBA is "VB & VBA in a Nutshell: The Language" by Paul Lomax (ISBN: 1-56592-358-8). --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ### Office automation in xojo vs. vb There is an implied Application instance when you write VBA code from within Excel, PowerPoint, or Word. For example this is VBA code running in PowerPoint: ``` xojo 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) ``` In PowerPoint, 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. Here's the Xojo code: ``` xojo Var PowerPoint As New PowerPointApplication Var pres As OLEObject Var slide1 As OLEObject pres = PowerPoint.Presentations.Add slide1 = pres.Slides.Add(1, Office.ppLayoutText) ``` There's really only two differences here. First, all objects are now OLEObjects, so whether it's a Presentation or Slide, you can create them all as of type OLEObject. All constant values are in the Office module. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ### Passing parameters by name Xojo doesn't support passing parameters by name; however you can still achieve this with a bit of work. First of all, you have to understand how to use the OLEObject. Here's an example that you can model after. Let's record a find and replace macro in Word and translate it to Xojo. This is the VB code: ``` xojo 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 it looks like in Xojo. ``` xojo Var word As New WordApplication Var find As OLEObject 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 ' Now the fun stuff 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 too it. Obviously the most painful bit is finding the correct position of that named parameter. That's about the only time when you really need to launch VBA and look it up in their Object Browser. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ### Troubleshooting Here is a procedure to verify that the OLE libraries are installed. One way is to launch 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: \# Launch Word. \# Launch 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: ``` xojo Var obj As Object Set obj = CreateObject("PowerPoint.Application") obj.Activate ``` Run the program and click on the button. If PowerPoint starts and you don't get any errors, then the OLE libraries are installed. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ### Error handling Errors come through OLE, so you need to handle `OLEExceptions`. This will report the last command that failed along with any additional information about the exception. ``` xojo Try Var word As New WordApplication word.ShowClipboard Catch err As OLEException MessageBox(err.Message) End Try ``` ## Compatibility | | | |-----------------------|-----------------------| | **Project Types** | Console, Desktop, Web | | **Operating Systems** | Windows |
Object parent class; `ExcelApplication`, `OLEContainer`, `OLEObject`, `OLEParameter`, `PowerPointApplication`, `WordApplication` classes.