Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

To practice by yourself, download the following workbook.


Step-by-Step Procedures to Perform Excel to PowerPoint Automation

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.

Make Dataset Ready

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.

Open Visual Basic Editor


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.

Referencing PowerPoint with Excel

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

Read More: How to Embed an Excel File in PowerPoint (5 Easy Ways)


Similar Readings


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.

Insert Module Window

Read More: How to Insert an Excel File into PowerPoint as an Icon


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

Type VBA Code

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.

Save and Run VBA

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

Read More: How to Open Existing PowerPoint File from Excel VBA


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 rightclick on the mouse.
  • Then, click Edit Picture.

Make Changes in PowerPoint

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

Excel to PowerPoint Automation Edit

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.

Final Output

  • Finally, we have renamed it “Sales Report” and this is our final result.


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. To read similar articles, check out the ExcelDemy website. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.


Related Articles

Sudipta Chandra Sarker

Sudipta Chandra Sarker

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel. Here I will be posting articles related to this. My educational degree is BSc in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, Bangladesh. I have a great interest in research and development. I always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo