Introduction
PowerPoint presentations often contain data and charts from Excel. Manually copying and pasting data between Excel and PowerPoint can be tedious and time-consuming. Fortunately, you can automate this process by using Visual Basic for Applications (VBA) macros to transfer data from Excel into PowerPoint slides.
This article provides step-by-step instructions on how to run VBA macros from Excel that will create PowerPoint slides populated with data.
Prerequisites
Before you can run Excel macros that create PowerPoint slides, you need:
- Excel and PowerPoint installed on your computer
- Basic knowledge of VBA macros
- Excel workbook with the data you want to transfer
- Access to the Developer tab in Excel and PowerPoint (enabled via File > Options > Customize Ribbon)
Enable the PowerPoint Object Library Reference
The first step is to set up a reference in Excel to the PowerPoint object model. This allows your Excel VBA code to communicate with PowerPoint.
Here are the steps:
- Open the VBA Editor in Excel by pressing Alt+F11
- Click Tools > References
- Scroll down and check the box next to “Microsoft PowerPoint xx.x Object Library” (xx.x depends on your Office version)
- Click OK
Create a Macro to Launch PowerPoint
Next, we can create a simple macro in Excel that will launch a new PowerPoint presentation:
Sub CreateNewPowerPoint()
'Create PowerPoint application object
Dim PowerPointApp As PowerPoint.Application
Set PowerPointApp = New PowerPoint.Application
'Make PowerPoint application visible
PowerPointApp.Visible = True
'Create new presentation
PowerPointApp.Presentations.Add
End Sub
This macro creates a PowerPoint application object, makes it visible, and adds a new blank presentation.
You can enhance this to save the new presentation to a specific location by adding a save method:
“`vb
PowerPointApp.ActivePresentation.SaveAs (“C:\Presentation1.pptx”)
## Transfer An Excel Range to PowerPoint
Once you have PowerPoint launched from Excel VBA, you can start transferring data over.
Here is an example macro that copies a range from Excel into a new PowerPoint slide:
vb
Sub CopyRangeToPowerPoint()
'Create PowerPoint application object
Dim PowerPointApp As PowerPoint.Application
Set PowerPointApp = New PowerPoint.Application
'Create new presentation
Dim PowerPointPres As PowerPoint.Presentation
Set PowerPointPres = PowerPointApp.Presentations.Add
'Add new slide
Dim PowerPointSlide As PowerPoint.Slide
Set PowerPointSlide = PowerPointPres.Slides.Add(1, 11)
'Copy range from Excel
ThisWorkbook.Worksheets("Sheet1").Range("A1:D5").Copy
'Paste range to PowerPoint slide
PowerPointSlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile
End Sub
This macro copies the specified range in Excel and pastes it into a new blank PowerPoint slide as an enhanced metafile picture.
The possibilities are endless for populating PowerPoint slides with Excel data using VBA. You can loop through rows, build multiple slides, insert charts, apply formatting, and more.
## Insert an Excel Chart Into PowerPoint
Here is an example that inserts a chart from Excel into a PowerPoint slide:
vb
Sub CopyChartToPowerPoint()
'Create PowerPoint application object
Dim PowerPointApp As PowerPoint.Application
Set PowerPointApp = New PowerPoint.Application
'Create new presentation
Dim PowerPointPres As PowerPoint.Presentation
Set PowerPointPres = PowerPointApp.Presentations.Add
'Add new slide
Dim PowerPointSlide As PowerPoint.Slide
Set PowerPointSlide = PowerPointPres.Slides.Add(1, 11)
'Copy chart from Excel
ThisWorkbook.Worksheets("Sheet1").ChartObjects("Chart 1").Copy
'Paste chart to PowerPoint and position
PowerPointSlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile
PowerPointSlide.Shapes(1).Left = 10
PowerPointSlide.Shapes(1).Top = 10
End Sub
This copies over an existing chart from Excel and pastes it into PowerPoint as an enhanced metafile picture. The chart is then positioned on the slide by setting its Left and Top properties.
## Run the PowerPoint Macro from Excel
The last step is to tie it all together by running your PowerPoint automation macro from Excel VBA.
Add this procedure which will call the other subs:
vb
Sub RunPowerPointMacro()
'Call PowerPoint automation subs
CreateNewPowerPoint
CopyRangeToPowerPoint
CopyChartToPowerPoint
'Release object references
Set PowerPointApp = Nothing
Set PowerPointPres = Nothing
Set PowerPointSlide = Nothing
End Sub
“`
Now from the Excel VBA editor, you can run the RunPowerPointMacro sub to launch PowerPoint and create the automated slides.
Conclusion
By leveraging VBA macros in Excel, you can save significant time automating the transfer and formatting of Excel data into PowerPoint slides.
The key steps are:
- Set up PowerPoint object library reference
- Create subs to launch PowerPoint and add slides
- Transfer over Excel ranges and charts
- Call the PowerPoint automation macros from a controller macro in Excel
With this foundation, you can build more advanced macros to produce complete, polished presentations straight from your Excel workbooks.
The ability to automate PowerPoint slide generation enables businesses to quickly turn Excel data into presentation decks for meetings, management reports, sales pitches, and more.