Controlling Office Applications from PowerPoint (by Naresh Nichani and Brian Reilly)
How can you control other Office programs from PowerPoint?
Access MVP Naresh Nichani and PowerPoint MVP Brian Reilly have kindly provided a useful example of controlling Office applications from within PowerPoint.
You can easily adapt the same code to work within any of the Office apps.
MS MVPs Naresh Nichani and Brian Reilly to the rescue
Their example files (ControllingOfficeFromPowerPoint.ppt and PPT Tools.MDB included in OfcFromPPT.zip) give working examples of automating Excel, Word, Outlook and Access from VBA code running in PowerPoint.
Download the file, save it to any convenient location and unzip the contents. Please keep both of the included files together in the same folder.
Notes
There are two ways of automating other applications: via Late or Early binding.
Early binding is faster and is somewhat simpler to program, since the Intellisense feature in the VBA IDE knows about the external application's objects and properties and can show them to you.
On the other hand, early binding requires that you set a reference to the application you want to automate (using Tools, References). That's no problem on your own system, and generally if your code runs on a system with the same or later version of the referenced application, it will work. But if the system has an earlier version of the referenced application, your code may not work.
Late binding is slower and doesn't offer Intellisense, but it doesn't require any specific version of an application, as long as you don't use methods and properties that earlier versions don't support.
I've modified the code slightly so that it can work with either late or early binding.
At the beginning of most of the modules, you'll see something like this:
'Private oXLApp As Excel.Application 'Private oWb As Excel.Workbook Private oXLApp As Object Private oWb As Object
For EARLY binding, set a reference to Microsoft Excel in Tools, References, uncomment the lines that declare variables as Excel objects (the first two lines) and comment out the lines that declare variables as Object.
Do this while running, modifiying and testing the code so you have the advantage of Intellisense.
Before distributing the code to others, comment out the first two lines, uncomment the second pair so your variables are declared as Object. Use Tools, References to remove the Excel reference.
Do the same for the other modules, but substitute Word, Outlook etc. for Excel above.
If you've used any constants from the other applications, you'll need to define them in your own code or substitute hard-coded values. See the CreateItem comments in the Outlook example code for an instance of this. Choosing Debug, Compile before saving your project should alert you to any such problems.