How to Perform Excel to PowerPoint Automation (With Easy Steps)

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.

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

Here, PwrPntAp, ippTile, iSlide are variables to indicate PowerPoint Application, Presentation and Slide respectively.

Set iPPTFile = PwrPntAp.Presentations.Add
Dim iSht As Worksheet

Here, 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, xlPicture

We 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 With

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

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.

Performing Automation.xlsm

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.

<< Go Back to Excel to PowerPoint | Export Data from Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!