Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 37 ยท Working with Word and Outlook

PowerPoint and Cross-Application Automation

Create PowerPoint slides from Excel data with VBA, and learn early and late binding, CreateObject, GetObject and how Office programs talk to each other.

Office programs can control each other. A macro in Excel can build a PowerPoint deck from a worksheet, fill a Word contract, or send an Outlook message. They all work the same way: you get an object for the other application, ask it for documents or items, and call its methods.

The key choice is how you connect to the other program: early binding (add a reference, get IntelliSense) or late binding (CreateObject, no reference needed). This lesson explains both, then builds PowerPoint slides from data.

VBA is read-only on this site. The runnable examples are JavaScript stand-ins that show how a slide deck structure is built from data, and the idea of late binding.

Early binding vs late binding

flowchart LR
  A["Your macro"] -->|"early: Tools > References"| B["Typed object: PowerPoint.Application"]
  A -->|"late: CreateObject"| C["Generic Object variable"]

Early binding gives IntelliSense and constants; late binding works on any machine with the program installed.

Early binding Late binding
Setup Tools > References > Microsoft PowerPoint xx Object Library None
Declaration Dim app As PowerPoint.Application Dim app As Object
Constants like ppLayoutText Available Use numbers (or define your own)
IntelliSense and compile-time checks Yes No
Version differences Can break on another Office version More tolerant

A good workflow: develop with early binding, then switch to late binding before distributing the file. Keep the constants you used in a Const block.

Starting or attaching to an application

VB
Dim ppt As Object
On Error Resume Next
Set ppt = GetObject(, "PowerPoint.Application")    ' attach to a running instance
On Error GoTo 0
If ppt Is Nothing Then
    Set ppt = CreateObject("PowerPoint.Application")  ' start a new one
End If
ppt.Visible = True

GetObject with an empty first argument attaches to an instance that is already running; CreateObject starts a new one. Test the result with Is Nothing. Release objects at the end with Set ppt = Nothing.

Building slides from Excel data

VB
Sub BuildDeck()
    Dim ppt As Object, pres As Object, sld As Object
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Summary")
    Dim r As Long

    Set ppt = CreateObject("PowerPoint.Application")
    Set pres = ppt.Presentations.Add

    For r = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        Set sld = pres.Slides.Add(pres.Slides.Count + 1, 2)   ' 2 = ppLayoutText
        sld.Shapes(1).TextFrame.TextRange.Text = ws.Cells(r, 1).Value2
        sld.Shapes(2).TextFrame.TextRange.Text = _
            "Total: " & Format(ws.Cells(r, 2).Value2, "#,##0")
    Next r

    pres.SaveAs ThisWorkbook.Path & "\Summary.pptx"
    pres.Close
    ppt.Quit
End Sub

A slide is a collection of shapes. Title and body placeholders are Shapes(1) and Shapes(2) for the standard layouts. Use Shapes.AddTextbox, AddPicture and AddChart2 to add more content.

Pasting an Excel chart or range

VB
ws.ChartObjects(1).Chart.Export ThisWorkbook.Path & "\chart.png"
sld.Shapes.AddPicture ThisWorkbook.Path & "\chart.png", False, True, 40, 120, 520, 320

Exporting a chart to an image and inserting that file is more reliable than clipboard copy/paste (Copy and Paste can fail when the clipboard is busy).

Talking to other programs

The same pattern works for any COM automation server:

Program ProgID
Word Word.Application
Excel Excel.Application
PowerPoint PowerPoint.Application
Outlook Outlook.Application
Access Access.Application
Dictionary Scripting.Dictionary
File system Scripting.FileSystemObject

Remember these rules:

  1. Qualify everything. Inside Excel, a bare Range means Excel's; to use another program's range, always go through its variable (wdApp.ActiveDocument.Range).
  2. Every program you start needs Quit, and every object variable should be set to Nothing when done.
  3. Keep other programs invisible (Visible = False) for unattended runs, visible while debugging.
  4. If a macro fails midway, hidden instances can stay in memory; a cleanup step in your error handler (see error handling) avoids this.

Try it: late binding and a deck from data

JS Build a slide deck structure from rows

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS Early vs late binding: constants

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Leaving PowerPoint or Word running in the background after an error.
  • Using the constants (ppLayoutText) in late-bound code without defining them.
  • Using unqualified Range/Selection and accidentally hitting the wrong application.
  • Depending on the clipboard instead of exporting files.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is an advantage of early binding?

    A library reference lets the editor know the object model, which helps while writing code.
  2. 2. Which call attaches to an already running PowerPoint?

    GetObject with an empty first argument returns a running instance, or fails if none exists.
  3. 3. Why distribute a workbook with late binding?

    Missing or mismatched references cause compile errors on other machines; late binding avoids them.
  4. 4. What should you do when your macro starts another Office program?

    Otherwise hidden instances can stay in memory.

Summary

  • Office programs expose COM objects; CreateObject starts one and GetObject attaches to one.
  • Early binding is great for development; late binding is better for distribution (define your own constants).
  • A slide is a set of shapes; use text ranges, pictures and exported chart images.
  • Always qualify objects, quit started programs and release variables.

Next: VBA UserForms Basics