Sometimes, we need a lot of Excel charts and cells into PowerPoint slides quickly and easily. If we use automation to link Excel and PowerPoint, we can skip copying and pasting between them. It will be much easier to analyze data. So, now we will discuss how to perform Excel to PowerPoint Automation in easy steps.
How to Perform Excel to PowerPoint Automation: Step-by-Step Procedures
To explain the steps, we will use a dataset that contains information about a company’s sales in 4 US states and a chart that shows this information. In STEP 1, you will see the dataset. Let’s follow the steps below to understand how it works.
STEP 1: Make Dataset Ready
- First of all, you need to make the dataset ready.
- To do so, insert the data in an Excel sheet and make a chart.
- Here, we have used the range B4:D8 to insert the sales information.
- Also, we have created a sales chart using the Insert tab.
- Now, we will automatically display the data and chart in PowerPoint.
Read More: [Solved] Embedded Excel in PowerPoint Not Showing All Data
STEP 2: Open Visual Basic Editor
- Secondly, we need to open the Visual Basic Editor in Excel.
- For that purpose, go to the Developer tab in the ribbon and select Visual Basic.
- We can also open it by pressing Alt + F11 on the keyboard.
- Furthermore, a laptop user can press Fn + Alt + F11 to open the Visual Basic Editor.
Read More:
STEP 3: Referencing PowerPoint with Excel
- For linking PowerPoint with Excel, we need to give access to Excel about PowerPoint.
- That is why, we’ll pick References from the Tools tab, and the following dialog box will appear.
- After that, check the box next to Microsoft PowerPoint 16.0 Object Library and press OK.
- Microsoft PowerPoint may differ depending on whatever version you are using.
STEP 4: Insert Module Window
- In the fourth step, from the Visual Basic editor, we’ll open the Module window.
- To do so, we will go to the Insert tab and pick Module.
- Then, the Module window will be displayed.
STEP 5: Type VBA Code
- Now, you need to use a VBA code.
- In the Module window, type the code below:
Option Explicit
Sub Excel_to_PP()
Dim PwrPntAp As New PowerPoint.Application
Dim iPPTFile As PowerPoint.Presentation
Dim iSlide As PowerPoint.Slide
Set iPPTFile = PwrPntAp.Presentations.Add
Dim iSht As Worksheet
For Each iSht In ThisWorkbook.Sheets
If iSht.Name <> "Setting" Then
SetiSlide=iPPTFile.Slides.AddSlide(1,iPPTFile.SlideMaster.CustomLayouts(6))
iSlide.MoveTo (iPPTFile.Slides.Count)
With iSlide.Shapes.Title
.TextFrame.TextRange.Text = iSht.Name
.TextFrame.TextRange.Font.Color.RGB = RGB(255, 255, 255)
.Fill.BackColor.RGB = RGB(150, 150, 150)
.TextEffect.Alignment = msoTextEffectAlignmentCentered
.TextEffect.FontName = "Calibri"
.Height = 50
End With
iSht.UsedRange.CopyPicture xlScreen, xlPicture
iSlide.Shapes.Paste
With iSlide.Shapes(2)
.LockAspectRatio = msoCTrue
.Width = iPPTFile.PageSetup.SlideWidth - 30
.Top = 0
If .Height > iPPTFile.PageSetup.SlideHeight Then
.Height = iPPTFile.PageSetup.SlideHeight - 120
End If
.Left = 0
If .Width > iPPTFile.PageSetup.SlideWidth Then
.Width = iPPTFile.PageSetup.SlideWidth - 30
End If
.Left = (iPPTFile.PageSetup.SlideWidth - .Width) / 2
.Top = 100
End With
End If
Next
End Sub
VBA CODE Explanation:
Dim PwrPntAp As New PowerPoint.Application
Dim iPPTFile As PowerPoint.Presentation
Dim iSlide As PowerPoint.SlideHere, PwrPntAp, ippTile, iSlide are variables to indicate PowerPoint Application, Presentation and Slide respectively.
Set iPPTFile = PwrPntAp.Presentations.Add
Dim iSht As WorksheetHere, we are opening PowerPoint Application and declaring a variable for Excel worksheet.
If iSht.Name <> "Setting" Then
Set iSlide = iPPTFile.Slides.AddSlide(1, iPPTFile.SlideMaster.CustomLayouts(6))
iSlide.MoveTo (iPPTFile.Slides.Count)
With iSlide.Shapes.Title
.TextFrame.TextRange.Text = iSht.Name
.TextFrame.TextRange.Font.Color.RGB = RGB(255, 255, 255)
.Fill.BackColor.RGB = RGB(150, 150, 150)
.TextEffect.Alignment = msoTextEffectAlignmentCentered
.TextEffect.FontName = "Calibri"
.Height = 50
End With
iSht.UsedRange.CopyPicture xlScreen, xlPictureWe are copying the Excel data and creating the PowerPoint page where we will place our data in this section.
iSlide.Shapes.Paste
With iSlide.Shapes(2)
.LockAspectRatio = msoCTrue
.Width = iPPTFile.PageSetup.SlideWidth - 30
.Top = 0
If .Height > iPPTFile.PageSetup.SlideHeight Then
.Height = iPPTFile.PageSetup.SlideHeight - 120
End If
.Left = 0
If .Width > iPPTFile.PageSetup.SlideWidth Then
.Width = iPPTFile.PageSetup.SlideWidth - 30
End If
.Left = (iPPTFile.PageSetup.SlideWidth - .Width) / 2
.Top = 100
End WithAnd now we’re pasting the copied data from Excel into PowerPoint and formatting the representation.
STEP 6: Save and Run VBA
- Now, we need to save the code first and then, run it.
- To save the code, simply hit Ctrl + S on the keyboard.
- After that, press the F5 key to run the code.
- Otherwise, we can simply click the “run” button shown below after saving.
- After that, PowerPoint will open automatically and sales data and chart will be displayed over PowerPoint.
However, if we want to make the data look better, we can edit it in the PowerPoint.
STEP 7: Make Changes in PowerPoint
- In the following step, we will make the necessary changes to the slides.
- We will put our cursor on the PowerPoint figure and right-click on the mouse.
- Then, click Edit Picture.
- After that, a window will come out and throw us a question and we will respond to it with “Yes”.
- We can now change the length, width, and zoom in and out by dragging the circle ends that show the picture’s end sides.
- Also, we have increased the length and width to improve the appearance of the figure and make the data more understandable.
Read More: How to Update Charts in PowerPoint from Excel Automatically
Final Output
- After editing, the following figure is our desired figure.
- We have put the data and a chart in Excel, and we have been able to put the information in PowerPoint in an automated way.
- Finally, we have renamed it “Sales Report” and this is our final result.
Download Practice Workbook
To practice by yourself, download the following workbook.
Conclusion
In this article, we have demonstrated step-by-step procedures for Excel to PowerPoint Automation. We trust that integrating Excel and PowerPoint into your workflow is now a breeze. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.
