XSCD
12-29-2020, 02:24 PM
vba (https://stackoverflow.com/questions/tagged/vba) charts (https://stackoverflow.com/questions/tagged/charts) powerpoint (https://stackoverflow.com/questions/tagged/powerpoint) editing (https://stackoverflow.com/questions/tagged/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
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