How to Copy a Chart From Excel to PowerPoint With VBA

 Hi all,

Today we will discuss on two powerful tools; Microsoft Excel and PowerPoint. These tools are often used in conjunction for creating reports, presentations, and data visualizations. So lets start with coding..

What is VBA?

VBA is a programming language that allows you to automate tasks in Microsoft Office packages like Excel, PowerPoint, Word, and Access. It permits you to write scripts that manage those programs, perform complicated operations, and automate repetitive duties.

Steps to Copy a Chart from Excel to PowerPoint the use of VBA

1. Open Excel and Enable Developer Tab

Open Excel.

Go to File > Options.

In the Excel Options conversation, select Customize Ribbon.

Check the container subsequent to Developer in the right column.

Click OK.

2. Open the VBA Editor

Click on the Developer tab in Excel.

Click on Visual Basic to open the VBA editor.

3. Write VBA Code

Copy and paste the subsequent VBA code into the editor:

Sub CopyChartToPowerPoint()

    Dim PowerPointApp As Object

    Dim PowerPointPres As Object

    Dim PowerPointSlide As Object

    Dim Chart As ChartObject

    ' Initialize PowerPoint

    Set PowerPointApp = CreateObject("PowerPoint.Application")

    PowerPointApp.Visible = True ' Set to False if you don't want to see PowerPoint

    ' Create a new PowerPoint presentation

    Set PowerPointPres = PowerPointApp.Presentations.Add

    ' Add a slide to the presentation

    Set PowerPointSlide = PowerPointPres.Slides.Add(1, 1)

    ' Copy the chart from Excel

    Set Chart = ThisWorkbook.Sheets("Sheet1").ChartObjects("Chart 1")

    Chart.CopyPicture Appearance:=xlScreen, Format:=xlPicture

    ' Paste the chart into PowerPoint

    PowerPointSlide.Shapes.Paste

    ' Adjust the position and size of the chart (if needed)

    ' For example:

    ' PowerPointSlide.Shapes(1).LockAspectRatio = msoFalse

    ' PowerPointSlide.Shapes(1).Left = 100

    ' PowerPointSlide.Shapes(1).Top = 100

    ' Release objects

    Set PowerPointSlide = Nothing

    Set PowerPointPres = Nothing

    Set PowerPointApp = Nothing

    Set Chart = Nothing

End Sub

4.    Customize the Code (if necessary)

•   Replace "Sheet1" with the name of the Excel sheet containing your chart.
•   Replace "Chart 1" with the name of your chart.

5.     Run the Macro

       Close the VBA editor and go back to Excel. Select the Developer tab, click           on Macros, and run `CopyChartToPowerPoint`.

Explanation of the Code

• We create objects to symbolize the Excel utility, PowerPoint utility, the PowerPoint presentation, and the chart.
• PowerPoint is initialized and set to be seen.
• A new PowerPoint presentation is created.
• A slide is added to the presentation.
• The chart is copied as a photo from Excel.
• The photo is pasted into the PowerPoint slide.
• Optionally, you could modify the position and length of the chart.
• Finally, the objects are released to unfastened up memory.