Macro 101: Convert a Workbook into a PowerPoint Presentation
This macro takes the concept of using Excel data in PowerPoint to the extreme. Open the sample
workbook called Macro 101 Convert a Workbook into a PowerPoint Presentation.xlsm. In this work-
book, notice that each worksheet contains its own data about a region. It’s almost like each work-
sheet is its own separate slide, providing information on a particular region.
The idea here is that you can build a workbook in such a way that it mimics a PowerPoint presentation;
the workbook is the presentation itself and each worksheet becomes a slide in the presentation.
After you do that, you can easily convert that workbook into an actual PowerPoint presentation using
a bit of automation.
With this technique, you can build entire presentations in Excel, where you have better analytical
and automation tools. Then you can simply convert the Excel version of your presentation to a
PowerPoint presentation.
How it works
Before you implement this macro in your workbook, you need to set a reference to the Microsoft
PowerPoint Object Library. Again, you can set the reference by opening the Visual Basic Editor in
Excel and selecting Tools➜References. Scroll down until you find the entry Microsoft PowerPoint XX
Object Library, where the XX is your version of PowerPoint. Select a check box next to the entry.
Sub Macro101()
‘Step 1: Declare your variables
Dim pp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim xlwksht As Excel.Worksheet
Dim MyRange As String
Dim MyTitle As String
‘Step 2: Open PowerPoint, add a new presentation and make visible
Set pp = New PowerPoint.Application
Set PPPres = pp.Presentations.Add
pp.Visible = True
‘Step 3: Set the ranges for your data and title
MyRange = “A1:I27”
‘Step 4: Start the loop through each worksheet
For Each xlwksht In ActiveWorkbook.Worksheets
xlwksht.Select
Application.Wait (Now + TimeValue(“0:00:1”))
MyTitle = xlwksht.Range(“C19”).Value
‘Step 5: Copy the range as picture
xlwksht.Range(MyRange).CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
‘Step 6: Count slides and add new slide as next available slide number
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutTitleOnly)
PPSlide.Select
‘Step 7: Paste the picture and adjust its position
PPSlide.Shapes.Paste.Select
pp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters,
True pp.ActiveWindow.Selection.ShapeRange.Top = 100
‘Step 8: Add the title to the slide then move to next worksheet
PPSlide.Shapes.Title.TextFrame.TextRange.Text = MyTitle
Next xlwksht
‘Step 9: Memory Cleanup
pp.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set pp = Nothing
End Sub
1. Step 1 declares six variables: PP is an object variable that exposes the PowerPoint Application
object; PPPres is an object variable that exposes the PowerPoint Presentation object;
PPSlide is an object variable that exposes the PowerPoint Slide object; xlwksht is an
object variable that exposes the Worksheet object; MyRange is a string variable used to
store and pass a range name as a string; and MyTitle is a string variable used to store and
pass a title for each slide.
2. Step 2 opens PowerPoint with an empty presentation. Note that we are setting the
Visible property of the PowerPoint application to True. This ensures that we can see the
action as the code runs.
3. Step 3 fills the MyRange variable with a string representing the range we want to capture as
the slide content. We also fill the MyTitle variable with the value of cell C19. The value here
becomes the title for the slide.
4. Step 4 starts the loop through each worksheet in the workbook. The loop stops when all
worksheets have been looped through. Note that we are using the Application.Wait
method, telling the macro to pause for a second. This allows the chart to render completely
before the range is copied.
5. Step 5 uses the CopyPicture method to copy our specified range as a picture.
6. Step 6 adds a new slide to the presentation using the Add method of the Slide object.
Note that we are using SlideCount+1 to specify the index number of the added slide.
Using SlideCount+1 allows us to dynamically assign the next available number as the
slide index. Also note that we are using ppLayoutTitleOnly, ensuring our slide is cre-
ated with a title text frame.
7. Step 7 pastes the picture into the active slide, centers the picture horizontally, and adjusts
the picture vertically 100 pixels from the top margin.
8. Step 8 passes the MyTitle variable to apply text to the title text frame.
9. Step 9 releases the objects assigned to the variables, reducing the chance of any problems
caused by rogue objects that may remain open in memory.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
