none
MS Graph SeriesCollection Values RRS feed

  • Question

  • Dear All

    I'm struggling with a graph, where i try to read the values in the series. Can anybody tell me why is this not working?

        
     s = Me.SPIGraph.SeriesCollection(1).Values

    neither

     s = Me.SPIGraph.Object.Application.Chart.SeriesCollection(1).Values

    Seems there is a difference in object handling for Excel and Access. I tried different variable declarations - no luck

    I tried to adopt following to run under Access 2010 x32 VBA - no luck.

    Dim cht As Chart, s As Series, p As Point
    Dim vals, x As Integer
    
        Set cht = ActiveSheet.ChartObjects(1).Chart
        Set s = cht.SeriesCollection(1)
    
        vals = s.Values
    
        For x = LBound(vals) To UBound(vals)
          If vals(x) > 10 Then
            With s.Points(x)
                .MarkerBackgroundColor = RGB(255, 0, 0)
                .MarkerForegroundColor = RGB(255, 0, 0)
            End With
          End If
        Next x

    Any thoughts?

    Greg

    Sunday, October 22, 2017 9:29 PM

All replies

  • Hi SysGreg,

    Chart in access is a Graph.Chart object which is different from chart in Excel. This chart does not have Values property. I did not find any property which could return value of the chart in access. The only workaround I could do is using the text of Point.DataLable as the value.

    Here is the example.

    Private Sub Command0_Click()
    
    Dim cht As Graph.Chart
    
    Dim s As Graph.Series
    
    Dim p As Graph.Point
    
    Set cht = Me.Graph4.Object
    
    Set s = cht.SeriesCollection(1)
    
    For Each p In s.Points
    
    'make sure the point has datalabel
    
    p.HasDataLabel = True
    
    pval = p.DataLabel.Text
    
    MsgBox pval
    
    'you could also hide the datalabel after getting the value
    
    p.HasDataLabel = False
    
    Next p
    
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 23, 2017 7:00 AM
  • Thats what i was suspected. Did you ever tried to apply the Excel Graph thru Office Web Components? There is a reference to it in the Access and VBA. Wonder if that could bring Excels capabilities?

    Your solution would work with point labels, which i do not use. In that case i will use a workaround by reading the values from the underlying row source and dependently of value i will alter the specific points color

    Greg

    Monday, October 23, 2017 6:53 PM
  • Hi SysGreg,

    >>Wonder if that could bring Excels capabilities?

    I am afraid it would not.

    >>In that case i will use a workaround by reading the values from the underlying row source and dependently of value i will alter the specific points color

    Thanks for sharing this workaround, I would suggest you mark your reply as answer, and then other who run into the same issue would find the solution easily.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, October 24, 2017 10:07 AM