Last Updated on February 2, 2022 – 3 min read
Can we use VBA in PowerPoint? VBA can communicate between Microsoft Office tools such as Word, PowerPoint, Excel, Outlook.
In this article, you will discover to use PowerPoint from Excel with a VBA macro.
Most Excel users spend time processing data in Excel to create a report in PowerPoint. If you repeat this task often, this article is for you. How to save time by automatically creating your PowerPoint reporting from the Excel tool?
You can automate repetitive tasks in PowerPoint with VBA. For example, you have fifty pages to delete or update several objects on each slide, the VBA language can do it in a few seconds.
To use PowerPoint from Excel you need to activate the reference Microsoft Powerpoint x.x Object Library. Go to the Macro editor -> Tools -> Reference and tick the line Microsoft Powerpoint X.X Object Library (X.X depends on the version of Office installed on your computer).
Create a PowerPoint presentation from Excel with VBA
To create a PowerPoint presentation, use the CreateObject to get a reference to an ActiveX object.
The procedure below shows you how to create a PowerPoint presentation by first creating a PowerPoint application.
Your presentation is composed of slides objects with Text Zone objects. The code closes automatically PowerPoint.
Don’t forget the good practices of turning off alert messages and refreshing the screen to save time.
Sub CreatePowerPointPresentation()
'Create a Powerpoint presentation
'Remove the update of Excel screen
Application.ScreenUpdating = False
Application.EnableEvents = False 'Remove pop-up
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
'Remove the automatic calculation
Application.Calculation = xlCalculationManual
'Declaration
Dim PptApp, PptPres, PptDoc As Object
Dim PptLayout, PptSlide, Sh As Object
'Open the PowerPoint application
Set PptApp = CreateObject("Powerpoint.Application")
PptApp.Visible = True
PptApp.Activate
'Add a new presentation
Set PptPres = PptApp.Presentations.Add
Set PptDoc = PptApp.ActivePresentation
'Count the number of slides
SlideCount = PptDoc.Slides.Count
'Add a new slide
Set PptSlide = PptDoc.Slides.Add(SlideCount + 1, 2)
PptSlide.Select
With PptDoc
'Add a slide
.Slides.Add Index:=2, Layout:=2
' Create a text zone
Set Sh = .Slides(1).Shapes.AddLabel( _
Orientation:=msoTextOrientationHorizontal, _
Left:=100, Top:=100, Width:=150, Height:=60)
'Insert the value in A1 in a Text Zone
Sh.TextFrame.TextRange.Text = Range("A1")
'Modify the text colour
Sh.TextFrame.TextRange.Font.Color = RGB(255, 100, 255)
'Add a new slide in the second position
Set Diapo = .Slides.Add(Index:=2, Layout:=2)
End With
'Save the presentation
PptDoc.SaveAs FileName:=ThisWorkbook.Path & "\" & _
"NewPresentation.ppt"
PptDoc.Close 'Close the presentation
PptApp.Quit 'Close PowerPointApplication
'Free variables
Set PptPres = Nothing
Set PptDoc = Nothing
Set PptSlides = Nothing
'To be re-activated at the end of the process
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True
Application.Calculation = xlCalculationAutomatic
End Sub
Save a PowerPoint presentation in a JPEG format
You can save a slide of your PowerPoint presentation in a JPEG format or a PDF by using the Export method on the slides object. This method requires as an argument the path to save.
Sub SavePresPPtAsJpeg()
Presentation("Finance_Pres").Slides(1).Export Filename:="C:\Desktop", _
Filtername:="JPG"
End Sub
Insert existing slides in a PowerPoint presentation
Do you need to automatically insert slides into your presentation?
To insert slides automatically at a specified position in your PowerPoint you can use the InsertFromFile method of the slides object after opening your file.
Sub InsertSlides()
'Insert slides in the presentation at a specific position
Dim PptDoc As Presentation
Set pptDoc = Application.Presentations.Open(FileName:="C:\Finance_Pres.ppt")
'InsertFromFile(FileName, Index, SlideStart, SlideEnd)
PptDoc.Slides.InsertFromFile "C:\Finance_calculation.ppt", 2, 1, 4
'Free variables
Set PptDoc = Nothing
End Sub
Get the coordinates of the slide’s center
When working on your presentation, you can insert an element such as a graph, a title, a text area, a table at a specific position in your slide.
The procedure below shows you to get the center coordinate of your slide.
Sub CenterOfSlide ()
'Get the middle coordinates
Dim PptDoc As Presentation
Set PptDoc = Application.Presentations.Open(FileName:="C:\Finance_Pres.ppt")
'Middle in function of the Height
PptDoc.PageSetup.SlideHeight / 2
'Middle in function of the Width
PptDoc.PageSetup.SlideWidth / 2
'Free variables
Set PptDoc = Nothing
End Sub
Manipulate specific slides
To wrap around the slides, you can define multiple Slides through a Slide Range objectify assigning an array. You can loop on the slides defined in your array and insert code instructions by using a For Each.
In the example below, we loop on slides 1, 4, and 6.
Sub LoopOnSlides()
'Loop on defined slides
'Declaration
Dim PresSld As SlideRange
Dim Sld As Slide
Dim Shp As Shape
'Initialisation
Set PresSld = ActivePresentation.Slides.Range(Array(1, 3, 5))
'Loop on each slide
For Each Sld In PresSld
Set Shp = Sld.Shapes.AddTextbox(msoTextOrientationHorizontal, 25, 25, 300, 50)
With Shp.TextFrame.TextRange
.Text = "Bitcoin maps"
.Font.Italic = msoTrue
End With
Next Sld
Set Shp = Nothing
End Sub
Close a PowerPoint presentation
To close your presentation automatically, use the Close method.
Sub ClosePresentation()
ActivePresentation.Close
End Sub
Add a text zone
To insert a text zone you can use the AddTextBox method of the Shapes object.
Sub AddTextZone()
'Add a text zone in a slide
'Declaration
Dim Sld As Slide
Dim Shp As Shape
'Initialisation
Set Sld = ActivePresentation.Slides(1)
'Create Text Zone
Set Shp = Sld.Shapes.AddTextbox(msoTextOrientationHorizontal, 50, 50, 300, 50)
'Layout the text zone
With Shp.TextFrame.TextRange
.Text = "News Finance !" 'add text
.Font.Name = "Comic sans MS" 'police
.Font.Bold = msoTrue 'bold
.Font.Italic = msoTrue 'italic
.Font.Size = 15 'size text
End With
Set Shp = Nothing
Set Sld = Nothing
End Sub
Print a PowerPoint presentation from Excel with VBA
You can use the PrintOut method for the Presentation object to automatically print your presentation.
Sub PrintPresentation()
'Print a presentation
Application.ActivePresentation.PrintOut 1, 3
End Sub
Get Excel data in PowerPoint
Do you prepare a report based on data contained in an Excel workbook? You are always switching between PowerPoint and Excel to copy-paste data from your sheet in your presentation.
How do you copy a range and insert the value of cells in a text zone of your presentation?
Sub GetExcelDataInYourPresentation()
'Declaration
Dim PptApp As PowerPoint.Application
Dim PptDoc As PowerPoint.Presentation
'Initialisation
Set PptApp = CreateObject("Powerpoint.Application")
PptApp.Visible = True
Set PptDoc = PptApp.Presentations.Open("C:\Finance_Pres.ppt")
With PptDoc
'copy cells from the Excel sheet
Feuil1.Range("B1:H5").Copy
'Paste in the slide
.Slides(2).Shapes.Paste
End With
'Layout
With PptDoc.Slides(2).Shapes(.Slides(2).Shapes.Count)
.Name = "Weather" 'Rename the table pasted
.Left = 150 'Horizontal position in the slide
.Top = 100 'Vertical position in the slide
.Height = 300 'Height
.Width = 400 'Width
End With
'Insert cell A1 in the text zone of the slide 4
PptDoc.Slides(4).Shapes(2).TextFrame.TextRange.Text = Range("A1").Value2
'Free variables
Set PptDoc = Nothing
Set PptApp = Nothing
End Sub
Save a presentation
You can use the Save method of the Presentation object to save again your presentation after modifications.
Sub SavePresentation ()
'Declaration
Dim PptApp As PowerPoint.Application
Dim PptDoc As PowerPoint.Presentation
'Initialisation
Set PptApp = CreateObject("Powerpoint.Application")
Set PptDoc = PptApp.Presentations.Open("C:\Finance_Pres.ppt")
PptDoc.Save 'save the presentation
PptDoc.Close 'close the presentation
PptApp.Quit 'close Powerpoint application
'Free variables
Set PptApp = Nothing
Set PptDoc = Nothing
End Sub
Update graphiques data
You are working on several charts based on several data updated a few times ago. Following changes to your data, you need to update your graphics.
You can refresh your data source of your charts.
Sub UdpateGraphicsInPres()
'Declarations
Dim PptApp As PowerPoint.Application
Dim Shp As PowerPoint.Shape
Dim Wb As Workbook
'Initialisation
Set PptApp = CreateObject("PowerPoint.Application")
PptApp.Visible = msoTrue
PptApp.Presentations.Open "C:\Finance_Pres.ppt"
'Copy a range
ActiveSheet.Range("A1:G10").Copy
With PptApp.ActivePresentation.Slides(1)
'Loop on shapes to retrieve the graph
For Each Shp In .Shapes
If Shp.Type = msoEmbeddedOLEObject Then
'Check if it is a graphe sheet
If Shp.OLEFormat.progID = "Excel.Chart.8" Then
Set Wb = Sh.OLEFormat.Object
'Paste data
Wb.Sheets("Feuil1").Range("A1").PasteSpecial
'Active the first sheet with graphe
Wb.Sheets(1).Activate
End If
End If
Next Sh
End With
'Free variables
Set PptApp = Nothing
End Sub
Insert an Excel file in a PowerPoint
You send your presentation to multiple recipients using Outlook.
To send one attachment, you can attach your data inside your presentation. You can insert your file directly into the PowerPoint. To do it, you can use the AddOLEObject method of the Shapes object.
You can also automate the sending of your presentation with a VBA procedure using Outlook, as in the following article.
Sub InsertExcelFileInPresentation()
'Declaration
Dim PptApp As PowerPoint.Application
Dim PptDoc As PowerPoint.Presentation
'Initialisation
Set PptApp = CreateObject("PowerPoint.Application")
PptApp.Visible = True
Set PptDoc = PptApp.Presentations.Open(Filename:="C:\Desktop" & _
"Finance_Pres" & ".pptx")
'Insert the excel file in the slide 3
PptDoc.Slides(3).Shapes.AddOLEObject Left:=100, Top:=100, Width:=200, _
Height:=300, Filename:="C:\Desktop\ExcelFile\FinanceData.xlsx", DisplayAsIcon:=True
PptDoc.Save 'save the presentation
PptApp.Quit 'Close the application
'Free variables
Set PptDoc = Nothing
Set PptApp = Nothing
End Sub
SKILLS 