CODEWITHSUNDEEP
vbaexcel
braceyourself
braceyourself

Reputation: 257

How to handle Powerpoint chart from Excel

I have a PowerPoint file and a chart in the second slide. I am not able to set height and width of the chart while writing macro in an Excel file. Below is the code I am trying. Please note I need to modify height and width only from Excel macro.

Sub controlPPT()
    Dim PPT As Object
    Set PPT = CreateObject("PowerPoint.Application")     
    With Application.FileDialog(1)
        .AllowMultiSelect = False
        .Show
        .Filters.Clear
        .Filters.Add "PPT files", "*.pptx"
        .FilterIndex = 1
        If .SelectedItems.Count > 0 Then
            Set slideTwo = PPT.ActivePresentation.Slides(2)
            slideTwo.Shapes(1).Chart.PlotArea.Height = 120
            slideTwo.Shapes(1).Chart.PlotArea.Width = 200
            slideTwo.Shapes(1).Chart.PlotArea.Left = 0
            slideTwo.Shapes(1).Chart.PlotArea.Top = 0
        End If
    End With
End Sub

Upvotes: 0

Views: 222

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

The main issue is that Application.FileDialog doesn't open selected file, you should open it explicitly:

Sub controlPPT()
    Dim pptApp As Object
    Dim pres As Object
    Dim slideTwo As Object

    Set pptApp = CreateObject("PowerPoint.Application")

    With Application.FileDialog(1)
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "PPT files", "*.pptx"
        .FilterIndex = 1
        .Show
        If .SelectedItems.Count > 0 Then
            Set pres = pptApp.Presentations.Open(.SelectedItems(1))
            Set slideTwo = pres.Slides(2)
            slideTwo.Select
            With slideTwo.Shapes(1).Chart.PlotArea
                .Height = 120
                .Width = 200
                .Left = 0
                .Top = 0
            End With
        End If
    End With

    'save/close presentation
    pres.Save
    pres.Close
    'clean up
    Set pres = Nothing
    pptApp.Quit
    Set pptApp = Nothing
End Sub

also for reliability I would change slideTwo.Shapes(1) to slideTwo.Shapes("Chart1") where "Chart1" should be replaced with actual chart name.

Upvotes: 1

Related Questions