Ms Access and Ms Excel VBA - Dynamic Charts

Hello everybody,

I have a strange problem. I must generate mutliple charts dynamically from a Ms Access database. The solution we wanted to implement is that we export all the Data in Excel, and from then we generate the charts. It's working fine when the charts are define statically. But when it's time to do it dynamically, it doesn't work.

I'll give you an example :

Set xlChartObj(4) = xls.Charts.Add
 
   cTypeChart = "Pie Chart"
   
   
   With xlChartObj(4)
      .ChartType = xlPieExploded
      
      .SeriesCollection.NewSeries
      .SeriesCollection(1).Name = "='Sheet1'!$A$4"
      .SeriesCollection(1).Values = "='Sheet1'!$H$4:$J$4"
      .SeriesCollection(1).XValues = "='Sheet1'!$H$3:$J$3"
      
      .ApplyLayout (6)
      
      .ChartTitle.Text = "Chart Example"
  End With

That code is working well. But, when I'm adapting my solution in a function like :

Private Sub ChartGenerator(ByRef xls As Application.Excel, ByRef xlChartObj As Excel.Chart, ByVal typeChart As Variant, ByVal titleChart As String, ByVal axisX As String, ByVal axisY As String, ByVal layout As Integer, collectionName, collectionX, collectionXVal)

   Dim x As Integer

   With xlChartObj
      
      .ChartType = typeChart
      
      .ClearToMatchStyle
      
      
      For x = 0 To (UBound(collectionName) - 1)
         
         MsgBox "collectionName : " & collectionName(x) & " collectionX : " & collectionX(x) & " collectionXVal : " & collectionXVal(x)
         
         .SeriesCollection(x).Name = collectionName(x)
         .SeriesCollection(x).XValues = collectionX(x)
         
          If x = 0 Then
            .SeriesCollection(1).XValues = collectionXVal(x)
         End If
         
         .SeriesCollection.NewSeries
         
      Next x
      
      .ApplyLayout (layout)
      
      .ChartTitle.Text = titleChart
      
      
      'Assignation du nom de l'axe des X
      .Axes(xlCategory, xlPrimary).AxisTitle.Text = _
        axeX
      
      'Assignation du nom de l'axe des Y
      .Axes(xlValue, xlPrimary).AxisTitle.Text = _
        axeY
   End With

End Sub

and the following code who calls the Procedure

Set xlChartObj(0) = xls.Charts.Add
   
   
 
   cTypeChart = "Line Chart"
   
   ReDim collectionName(3) As Variant
   ReDim collectionX(3) As Variant
   ReDim collectionXVal(3) As Variant
   
   collectionName(0) = "='Sheet1'!$B$3"
   collectionX(0) = "='Sheet1'!$B$4:$B$24"
   collectionXVal(0) = "='Sheet1'!$A$4:$A$24"
   
   collectionName(1) = "='Sheet1'!$C$3"
   collectionX(1) = "='Sheet1'!$C$4:$C$24"
   collectionXVal(1) = ""
   
   collectionName(2) = "='Sheet1'!$D$3"
   collectionX(2) = "='Sheet1'!$D$4:$D$24"
   collectionXVal(2) = ""
   
   ChartGenerator xls, xlChartObj(0), xlLine, "Example", "$", "Values", 1, collectionName, collectionX, collectionXVal

The problems is that I don't see the series at all in the chart and there is no error message. It's like it sees nothing. Do you have any idea, because it's working well with the static code ?

Thank you for your help !

Daniel