CODEWITHSUNDEEP
excelvbapowerpoint
Vkas
Vkas

Reputation: 1

Export Charts from Excel to PowerPoint on user defined Slides

I know this question has been asked before but as I am no VBA expert, I can't seem to get any of the solutions to work for my particular scenario so I was hoping someone could help.

I have some VBA code that copies a chart/Table selection from Excel, opens a PowerPoint presentation If it's open then active ppt else it creates new instance and then pastes the data into slides based on count and it counts the number of slides in that PowerPoint.

I am looking for a solution where it can pick slide number from table and paste the chart-based chart range, or the chart added in that table and copy paste that in PowerPoint for specific slides.

it works perfectly fine while running the code alone, but it can't work if i add define slide number.

Sub CreatePowerPointWithChart()'

   'First we declare the variables we will be using
       `Dim newPowerPoint As PowerPoint.Application`
       `Dim pre As PowerPoint.Presentations`
       `Dim Sld As PowerPoint.Slide`
      `Dim cht As Excel.ChartObject`
       `Dim SlideNumber As Long`
      

    'Look for existing instance
       On Error Resume Next
       Set newPowerPoint = GetObject(, "PowerPoint.Application")
       On Error GoTo 0

   'Let's create a new PowerPoint
       If newPowerPoint Is Nothing Then
           Set newPowerPoint = New PowerPoint.Application
       End If
   'Make a presentation in PowerPoint
       If newPowerPoint.Presentations.Count = 0 Then
           newPowerPoint.Presentations.Add
       End If

   'Show the PowerPoint
       newPowerPoint.Visible = True
       

   'Loop through each chart in the Excel worksheet and paste them into the PowerPoint
       For Each cht In ActiveSheet.ChartObjects

       'Add a new slide where we will paste the chart
           newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutTitleOnly
           newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
           Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)

       'Copy the chart and paste it into the PowerPoint as a Metafile Picture
           cht.Select
           ActiveChart.ChartArea.Copy
           activeSlide.Shapes.PasteSpecial(DataType:=ppPasteShape).Select

       'Set the title of the slide the same as the title of the chart
           activeSlide.Shapes(1).TextFrame.TextRange.Text = cht.chart.ChartTitle.Text

       'Adjust the positioning of the Chart on Powerpoint Slide
           newPowerPoint.ActiveWindow.Selection.ShapeRange.Left = Range("D11").Value
           newPowerPoint.ActiveWindow.Selection.ShapeRange.Top = Range("E11").Value
           newPowerPoint.ActiveWindow.Selection.ShapeRange.Width = Range("F11").Value
           newPowerPoint.ActiveWindow.Selection.ShapeRange.Height = Range("G11").Value

        'To Adjust Shapes
            ' activeSlide.Shapes(2).Delete
           ' activeSlide.Shapes(2).Left = 505
           ' activeSlide.Shapes(2).Height = 400

       Next

   Set activeSlide = Nothing
   Set newPowerPoint = Nothing

End Sub

Upvotes: 0

Views: 49

Answers (1)

Jon Peltier
Jon Peltier

Reputation: 6063

Rather than adding a slide and pasting the chart into the last slide, you would have a complete presentation, and pick the slide for a given chart.

I generally set up a table in Excel, with columns for slide number, content type (chart, range, text for a slide title, etc.), content worksheet, content reference (chart name, range address, text, etc.), and other information as needed. You need to populate a row for each content item that is copied into PowerPoint.

If the content type is a chart, then:

Worksheets(worksheet_name).ChartObjects(chart_name).Chart.CopyPicture _
    Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
newPowerPoint.ActivePresentation.Slides(slide_number).Shapes.Paste

Upvotes: 0

Related Questions