Search code examples
excelvbams-accesschartspowerpoint

add new data series to an existing excel chart object in powerpoint using vba 2010 - type mismatch

I am outputting an excel chart into powerpoint and I need to add a new data series to the Chart, I have recorded the macro to see how to do it, which it requires a range to a series collection but no luck.

Here is the full working example using access 2010 vba, in which at the end I try to add a new data series:

Option Compare Database

Public Sub CreateChart()
Dim myChart As Chart
Dim gChartData As ChartData
Dim gWorkBook As Excel.Workbook
Dim gWorkSheet As Excel.Worksheet
Dim pptApp As Object
Dim pptobj As Object

Set pptApp = CreateObject("Powerpoint.Application")
Set pptobj = pptApp.Presentation.Add

pptobj.Slides.Add 1, ppLayoutBlank

' Create the chart and set a reference to the chart data.
Set myChart = pptobj.Slides(1).Shapes.AddChart.Chart
Set gChartData = myChart.ChartData

' Set the Workbook and Worksheet references.
Set gWorkBook = gChartData.Workbook
Set gWorkSheet = gWorkBook.Worksheets(1)

 ' Add the data to the workbook.
gWorkSheet.ListObjects("Table1").Resize gWorkSheet.Range("A1:B5")
gWorkSheet.Range("Table1[[#Headers],[Series 1]]").Value = "Items"
gWorkSheet.Range("a2").Value = "Coffee"
gWorkSheet.Range("a3").Value = "Soda"
gWorkSheet.Range("a4").Value = "Tea"
gWorkSheet.Range("a5").Value = "Water"
gWorkSheet.Range("b2").Value = "1000"
gWorkSheet.Range("b3").Value = "2500"
gWorkSheet.Range("b4").Value = "4000"
gWorkSheet.Range("b5").Value = "3000"

' Apply styles to the chart.
With myChart
    .ChartStyle = 4
    .ApplyLayout 4
    .ClearToMatchStyle
End With

' Add the axis title.
With myChart.Axes(xlValue)
    .HasTitle = True
    .AxisTitle.Text = "Units"
End With

'Add a new data series - TYPE MISMATCH ERROR!!!
myChart.SeriesCollection.NewSeries
myChart.SeriesCollection(2).Name = "New_Series"
myChart.SeriesCollection(2).Values = gWorkSheet.Range("C2:C5") 'Range that is in the worksheet

'myChart.ApplyDataLabels

' Clean up the references.
Set gWorkSheet = Nothing
' gWorkBook.Application.Quit
Set gWorkBook = Nothing
Set gChartData = Nothing
Set myChart = Nothing

End Sub

This example is in the following link: https://msdn.microsoft.com/en-us/library/office/ff973127(v=office.14).aspx

In order to run this code is necessary to import: Visual Basic For Applications, Microsoft access Object Library, OLE Automation, Microsoft office access database engine object, Microsoft Office Object Library, Microsoft Powerpoint Object Library and Microsoft Excel Object Library. (Powerpoint object library should be imported first and then Microsoft Excel Object Library or there are reference problems)

Do you have any idea of how to add a new data series or what could be wrong with the code?

Thanks a lot in advance.

Solution

  • Try it this way:

    With myChart.SeriesCollection.NewSeries
  .name = "New_Series"
  .Values = gWorkSheet.Range("C2:C5").Value2
End With

    First you need to reference the newly added series and you cannot just assume that it will have index 2. This method is safer.

    Second take the .Value property of the said range, to get an array of values. This fixed it. The default .Value property of the Range object is not guaranteed to work in all circumstances, i.e. when the context or container is not Excel. So it is better to always be explicit when referencing the value of a range.