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 '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 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", _ Filtername:="JPG" 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 '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
At the end of your work, to close your presentation automatically, use the Close method.
Sub ClosePresentation() ActivePresentation.Close 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 '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 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() '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
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 () '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 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() '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 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() '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