Consulting

Results 1 to 1 of 1

Thread: Editing each chart element in PowerPoint VBA using data from Excel file

  1. #1
    VBAX Newbie
    Joined
    Dec 2020
    Posts
    2
    Location

    Editing each chart element in PowerPoint VBA using data from Excel file

    vba charts powerpoint editing



    I am new to VBA for PowerPoint and there is no option for recording the macros in PowerPoint. I have all the required data in an Excel file and have to generate multiple reports in PowerPoint with multiple charts similar to this line (I have other chart types as well Bar and Funnel) and have to update the following things in every chart.


    • Updating the chart data for different category and products for different reports from given Excel file.
    • Y axis scales to fit the data in every chart.
    • Having a choice for data points in series, i.e., number of points on the X axis.
    • The color of the data labels is conditionally formatted, for Example in Series Product 2 the point in Q4 2019 is red.

    I looked for references where I can find all the access to all the chart elements at once with scope of customization without success. It would be great help to have a sample code where I can update every element of chart at once much similar to what one does in Python/R.

    The code which i have written is below:

    Dim oPPApp As Object, oPPPrsn As Object, oPPSlide As Object
    Dim oPPShape As Object
    Dim FlName, path2, name_1 As String
    
    path2 = "C:\Users\Desktop\Quarterly_Report\PPT_Macro
    \data_for_charts.xlsx"
    FlName = "C:\Users\Desktop\Quarterly_Report\PPT_Macro
    \Report_2_View_3.pptx"
    Set wkb = Workbooks.Open(FileName:=path2, ReadOnly:=True)
    Set pptpres = pptApp.Presentations.Open(FlName, ReadOnly:=msoFalse)
    With ActivePresentation.Slides(3).Shapes("ChartObject4").Chart.ChartData
    
    .Workbook.Sheets(1).Range("A1:D10").Value = 
    wkb.Sheets(1).Range("I7:L16").Value
    
    
    End With
    
    name_1=
         ActivePresentation.Slides(3).Shapes("ChartObject4")_
        .Chart.SeriesCollection(3).Name
        With ActivePresentation.Slides(3).Shapes("ChartObject4").Chart.SeriesCollection(name_1).Points(9)
    
    
            .HasDataLabel = True
    
            .ApplyDataLabels Type:=xlValue
    
            .DataLabel.Font.Color = RGB(0, 255, 0)
    
    
    End With
    
    End Sub
    Attached Images Attached Images

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •