CODEWITHSUNDEEP
excelvbapowerpointexcel-charts
Mohamad Alkhatib
Mohamad Alkhatib

Reputation: 15

Export Excel charts as pictures to PowerPoint

I have in excel workbook with one chart per sheet and multiple sheets, I am trying to export all of the charts as pictures into PowerPoints The issue is that the current code I have doesn't work The error with my code:

  1. The PowerPoint opens as expected

  2. The chart of the active sheet I have selected before running is copied

  3. The copied chart is pasted in the PowerPoint

  4. the PowerPoint create the next Slide

  5. Now the issue the same chart from the same sheet is copied again! I am expecting the code to move to next sheet, select the chart on it, and copy past it in the next slide!

    Sub TEST_ExportChartsToPowerPoint_SingleWorkbook() 'Declare PowerPoint Object Variables Dim pptApp As PowerPoint.Application Dim pptPres As PowerPoint.Presentation Dim pptSlide As PowerPoint.Slide Dim SldIndex As Integer 'Declare Excel Object Variables Dim Chrt As ChartObject 'Dim Chrt As ChartArea Dim WrkSht As Worksheet

     'Create a new instance of PowerPoint
 Set pptApp = New PowerPoint.Application
     pptApp.Visible = True

 'Create a new Presentation within the PowerPoint Application
 Set pptPres = pptApp.Presentations.Add

 'Create an index handler for the slide creation
 SldIndex = 1

 'Loop through all of the worksheets in the active work book
 For Each WrkSht In Worksheets
     'WrkSht.Select

     ActiveChart.ChartArea.Select
     ActiveChart.ChartArea.Copy

     'Create a new slide, set the layout to blank, and paste the chart on the slide
     Set pptSlide = pptPres.Slides.Add(SldIndex, ppLayoutBlank)
     pptSlide.Shapes.Paste

     'Increment our slide index
     SldIndex = SldIndex + 1

 Next WrkSht

    End Sub

Upvotes: 0

Views: 454

Answers (1)

TehDrunkSailor
TehDrunkSailor

Reputation: 780

The issue is with how you are trying to reference your charts. Take a look at your loop:

'Loop through all of the worksheets in the active work book
    For Each WrkSht In Worksheets
        'WrkSht.Select
    
        ActiveChart.ChartArea.Select
        ActiveChart.ChartArea.Copy
    
        'Create a new slide, set the layout to blank, and paste the chart on the slide
        Set pptSlide = pptPres.Slides.Add(SldIndex, ppLayoutBlank)
        pptSlide.Shapes.Paste
    
        'Increment our slide index
        SldIndex = SldIndex + 1
    
     Next WrkSht

Even though you loop through all Worksheets, you only ever refer to the ActiveChart. You need to refer to the chart on the sheet in the loop. By default, ActiveChart references the chart on the active sheet. This is why the same chart is copied for each slide, because the reference never changes. So you need to change the reference to the current sheet in the loop.

Change the following lines

ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy

to

WrkSht.ChartObjects(1).Chart.ChartArea.Copy

This code properly references the chart. Also note that you don't need to use the .Select method of the ChartArea.

Upvotes: 0

Related Questions