The power of VBA lies in the fact that we can communicate between Microsoft Office tools such as Word, PowerPoint, Excel, Outlook … In this article, we will control PowerPoint from Excel using VBA macro. Some of codes can also be used directly on PowerPoint.

Using Excel, we often analyze the data that will be used to build a presentation. Most Excel users spend time using the data in Excel and then create a report in PowerPoint. If you repeat this task often, this article is for you. The objective of the article is to show you how to save time, by automatically creating your PowerPoint reporting starting from the Excel tool. You can then save your presentation in any format, both in the image and in PDF.

Each repetitive task in PowerPoint can be automated. For example, if you have a 50-pages presentation, and you have to delete the same object on each slide, in a few lines of code, VBA will do it for you in a second.

To use PowerPoint from Excel then you must first activate the reference “Microsoft Powerpoint x.x Object Library“. To do this, go to the macro editor: Tools -> Reference then tick the line “Microsoft Powerpoint X.X Object Library” and validate (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, you must first use the CreateObject function which creates and returns a reference to an ActiveX object.

The procedure below is quite complete since it shows you how to create a PowerPoint presentation by first creating an application, then a presentation composed of slide objects themselves having Text Zone objects to format. Then, the presentation closes automatically and the variables are released so as not to overload the memory.

Again, don’t forget the good practices of turning off alert messages as well as refreshing the screen.

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 jpeg

If you want to save a slide of your PowerPoint presentation in jpeg format or PDF, just use the Export method on Slides object which requires as an argument the path to save as well as the “JPG” format.

Sub SavePresPPtAsJpeg()

    Presentation("Finance_Pres").Slides(1).Export Filename:="C:\Desktop", _

End Sub

Insert existing slides in a PowerPoint presentation

Are you using an already existing presentation and would you like to automatically insert slides into it?

To insert slides automatically at a specified position in your PowerPoint presentation 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 will have to insert an element (a graph, a title, a text area, a table …) at a specific position in your slide.

The procedure below shows you the way to get the center coordinate of the slide. Of course, starting from this example, you can find other contact details on 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

Manipulates defined slides

To wrap around the slides, you can define multiple Slides through a Slide Range object to which you assign a table. Using a For Each… loop on the object defined above, you can loop on the slides defined in the table and insert your different instructions. 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

At the end of your work, to close your presentation automatically, use the Close method.

Sub ClosePresentation()


End Sub

Add a text zone

When preparing your slides you will always need to add text boxes. To insert automatically 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

Print a PowerPoint presentation from Excel with VBA

You have a presentation meeting tomorrow and you want to automatically print your work at the end?

To print your presentation, just use the PrintOut method of the Presentation object.

Sub PrintPresentation()
'Print a presentation

    Application.ActivePresentation.PrintOut 1, 3

End Sub

Get Excel data in a PowerPoint presentation

Are you preparing your presentation based on data contained in an Excel workbook? So, you are always switching between PowerPoint and Excel to copy-paste data from your sheet in your presentation.

To do it automatically you can follow the procedure below which explain how to copy a range in the slide and insert the value of a cell in a text zone of the 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

As seen before, if your presentation is already saved in a path of your computer, you can save it again after your modification by using the Save method of the Presentation object.

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 which have been updated a few times ago. Following changes to your data, you need to update your graphics. To do it, you just have to refresh the data source of your charts. The sample code below shows you how to do this update.

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 Presentation

You will send your presentation to multiple recipients using Outlook. To send only one attachment, you need to attach your data file inside your presentation. If you want to show your data in your presentation, you can insert your file directly in the PowerPoint. To do it, you just have to use the AddOLEObject method of the Shapes object.

If you also want to automate the sending of your presentation see this 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 )

Google photo

You are commenting using your Google 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