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).

Thailande – Photo taken by me

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
   Dim PptApp, PptPres, PptDoc As Object
   Dim PptLayout, PptSlide, Sh As Object

   'Open the PowerPoint application
   Set PptApp = CreateObject("Powerpoint.Application")
   PptApp.Visible = True

   '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)

   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 & "\" & _

   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

PowerPoint and Excel

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", _

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

    Dim PresSld As SlideRange
    Dim Sld As Slide
    Dim Shp As Shape
    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()


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
    Dim Sld As Slide
    Dim Shp As Shape
    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

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()
    Dim PptApp As PowerPoint.Application
    Dim PptDoc As PowerPoint.Presentation
    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
        'Paste in the slide
    End With
    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 ()

    Dim PptApp As PowerPoint.Application                
    Dim PptDoc As PowerPoint.Presentation
    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()
    Dim PptApp As PowerPoint.Application
    Dim Shp As PowerPoint.Shape
    Dim Wb As Workbook
    Set  PptApp = CreateObject("PowerPoint.Application")
    PptApp.Visible = msoTrue
    PptApp.Presentations.Open "C:\Finance_Pres.ppt"
    'Copy a range
    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
                    'Active the first sheet with graphe
                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()
    Dim PptApp As PowerPoint.Application                
    Dim PptDoc As PowerPoint.Presentation     
    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s