Using VBA to control other Office applications

Whilst this site is primarily about using Excel, there are times when Excel is not the best tool for the job.  Each Office application is designed for different tasks.  Tables and charts may be easily prepared in Excel, but they are often exported to PowerPoint, Word or Outlook for the purposes of presentation and distribution.  Whilst these are all different applications they all incorporate the VBA programming language.  This means that VBA in Excel can be used to control these other applications, and these other applications can also be used to control Excel.

This series of posts is all about how we can use VBA control other Office applications.

To be able to control other applications, Excel needs to know how to refer to the different objects within that application.  Excel doesn’t know this automatically, so we have to tell it to use the application’s object library.  For example, if we wanted Excel to create a new slide in a PowerPoint presentation, we first need to tell Excel to use the PowerPoint Object library.  This is process is known as binding.  Once Excel knows the PowerPoint Object Library, it is able to control PowerPoint through PowerPoint’s Object Model.  This is not just limited to PowerPoint; Word, Outlook, Adobe Acrobat and many other applications have Object Libraries, all of which Excel can use to speak their language.

When creating this link between Excel and the other application, there are two binding options (1) early binding (2) late binding.  This is what we will be considering for the rest of this post.

Early Binding

Within the VB editor it is possible to reference the Object Library for the Office applications.  This reference to the object library is saved within the Excel file itself.  Therefore, any time the file is opened the reference to the Object Library is still intact and is already available.

In the Visual Basic Editor select Tools -> References.

Select the reference for the application and click OK.

To control the application we can create a variable to hold the instance of the application.  The code to do this would be:

Dim pptApp As PowerPoint.Application
Set pptApp = New PowerPoint.Application

If the reference to the Object Library has not been created the following error message will appear.

Late Binding

When using Late Binding, it is not necessary to create a reference to the Object Library in advance.  By creating an object in VBA we can bind to the necessary Object Library at the time the code runs.

Dim pptApp As Object
Set pptApp = CreateObject("PowerPoint.Application")

Example – Controlling Powerpoint from Excel via VBA

This example code will open a new PowerPoint Presentation and write text onto the first slide.  This example users Late Binding

Sub ConnectToPowerpoint()

'Set the App as a variable using Late Binding
Dim pptApp As Object
Set pptApp = CreateObject("PowerPoint.Application")

'To use Early Binding use these two lines, rather than the lines above
'Dim pptApp As PowerPoint.Application
'Set pptApp = New PowerPoint.Application


'Create the Presentation as an Object
Dim pptPresentation As Object
Set pptPresentation = pptApp.Presentations.Add

'Create the Slide as an Object (1 slide, view type 2)
Dim pptSlide As Object
Set pptSlide = pptPresentation.Slides.Add(1, 2) '

'Make Powerpoint Visible
pptApp.Visible = True

'Select the first slide
pptSlide.Select

'Change the text on the first Slide
pptSlide.Shapes(1).TextFrame.TextRange.Text = "Hi PowerPoint"
pptSlide.Shapes(2).TextFrame.TextRange.Text = "Hi Excel, nice to meet you"

'Activate the Powerpoint Application
pptApp.Activate

'Release the memory
Set pptSlide = Nothing
Set pptPresentation = Nothing
Set pptApp = Nothing

End Sub

Advantages & Disadvantages of each type of Binding

It is not possible to pick one binding method and stick to it for the rest of your VBA career.  Each type of Binding has certain advantages, which make it better than the other in certain circumstances.  As there are only two options the advantages of one are generally the disadvantages of the other.

Early Binding – disadvantages

The Object Libraries are updated and changed each time Office is updated.  When this happens, the Object Libraries are given a new version number.  If the version of Excel on which the Object Library reference was created is different to the version on it is executed the code will not run correctly.  Excel, for example, might have been bound to version 12 of the object library, but the code might need to be run on a PC with version 14 installed.

Late Binding – disadvantages

Generally, the code runs slower and requires more processing power.  Excel does not know about the Object Library of the other application, so it does not bind until the code is executed.

It is harder to write the code in the VB Editor as it will not include the Intellisense options. (Intellisense is the name for the list of methods and properties which pop up in VB Editor when writing VBA code).

The VBA names for options and settings are not available.  For example, in PowerPoint VBA it is possible to paste as a Bitmap image using code ppPasteBitmap, however with Late Binding you must use the enumeration number, which is 1.  As a result, the code is less readable.

Which binding method should you choose?

Microsoft recommends using Early Binding where possible.

For me, the answer depends on whether I can guarantee which version of Object Library is available.  For example, where the macro will only be run on a single PC, or where there is consistent IT infrastructure and software, then Early Binding is a good option.  However, if the code is to be distributed and used on unknown IT software and infrastructure then Late Binding is the best option.

You need to be able to use both options to select the right version for your circumstances.

---

What next?

Discover how you can automate your work with our Excel courses and tools.