Excel – Scrolling Oracle Performance Charts

20 01 2010

January 20, 2010

This example shows how to generate scrolling charts in Excel that report performance data from V$OSSTAT, V$SYS_TIME_MODEL, and V$SYSSTAT.  This example retrieves 11 statistics from the three views, writes those values to a worksheet, and then calculates the delta values from the previous values read from the database – the last 20 delta values for each statistic are included in the charts.  While this example only generates 4 charts from the data, it is easy to extend the example to build additional charts.

With named cell ranges, it is not necessary to continually change the chart’s data values range, for example, you could create 4 named ranges in Excel and set those ranges as the values ranges for each of the charts:

ChartDBTime:     =IF(COUNTA(ScrollingChartData!$A:$A)>20,OFFSET(ScrollingChartData!$A$5,COUNTA(ScrollingChartData!$A:$A)-21,0,20),OFFSET(ScrollingChartData!$A$5,0,0,COUNTA(ScrollingChartData!$A:$A)-1))
ChartDBCPU:      =IF(COUNTA(ScrollingChartData!$B:$B)>20,OFFSET(ScrollingChartData!$B$5,COUNTA(ScrollingChartData!$B:$B)-21,0,20),OFFSET(ScrollingChartData!$B$5,0,0,COUNTA(ScrollingChartData!$B:$B)-1))
ChartSQLElapsed: =IF(COUNTA(ScrollingChartData!$C:$C)>20,OFFSET(ScrollingChartData!$C$5,COUNTA(ScrollingChartData!$C:$C)-21,0,20),OFFSET(ScrollingChartData!$C$5,0,0,COUNTA(ScrollingChartData!$C:$C)-1))
ChartParseTime:  =IF(COUNTA(ScrollingChartData!$D:$D)>20,OFFSET(ScrollingChartData!$D$5,COUNTA(ScrollingChartData!$D:$D)-21,0,20),OFFSET(ScrollingChartData!$D$5,0,0,COUNTA(ScrollingChartData!$D:$D)-1))

However, I will not use that approach in this example.

First, we need to name the first worksheet as ScrollingChartData and the second worksheet as ScrollingChart, and then create two ActiveX command buttons on the ScrollingChartData worksheet with the names cmdStart and cmdStop:

Next, we need to add a reference to the Microsoft ActiveX Data Objects as demonstrated here.  Also, we need to add a module, and name the module as mdlChartUpdater using the Properties window to assign the name (you can optionally name the two worksheets also).

Now, switch back to the Excel workbook, right-click the ScrollingChartData worksheet and select View Code.  In the Visual Basic editor, add the following code to the code for the worksheet:

Option Explicit

Private Sub cmdStart_Click()
    Dim lngResult As Long
    Dim objChartRange As Range

    On Error Resume Next

    'Clear out any of the old values

    ActiveWorkbook.Sheets("ScrollingChart").ChartObjects.Delete
    ActiveWorkbook.Sheets("ScrollingChartData").Range("4:10000").Clear

    With Sheets("ScrollingChart").ChartObjects.Add(10, 10, 400, 300)
        .Chart.SeriesCollection.NewSeries
        .Chart.Axes(1).CategoryType = 2
        .Chart.SeriesCollection(1).Values = "ScrollingChartData!A5:A5"

        .Chart.HasLegend = False

        .Chart.HasTitle = True
        .Chart.ChartTitle.Text = "DB Time"

        .Chart.Axes(xlCategory, xlPrimary).HasTitle = True
        .Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = ""
        .Chart.Axes(xlValue, xlPrimary).HasTitle = True
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = ""

        .Chart.SeriesCollection(1).HasDataLabels = True
        .Chart.SeriesCollection(1).HasLeaderLines = True

        With .Chart.PlotArea.Border
            .ColorIndex = 16
            .Weight = xlThin
            .LineStyle = xlContinuous
        End With

        .Chart.PlotArea.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=2, Degree:=0.756847486076142
        .Chart.PlotArea.Fill.ForeColor.SchemeColor = 23
        .Chart.PlotArea.Fill.Visible = True
        With .Chart.PlotArea.Border
            .ColorIndex = 57
            .Weight = xlThin
            .LineStyle = xlContinuous
        End With

        .Chart.SeriesCollection(1).Fill.OneColorGradient Style:=msoGradientVertical, Variant:=4, Degree:=0.2
        .Chart.SeriesCollection(1).Fill.Visible = True
        .Chart.SeriesCollection(1).Fill.ForeColor.SchemeColor = 4

        .Chart.Axes(xlValue).MajorGridlines.Border.ColorIndex = 2
        With .Chart.SeriesCollection(1).DataLabels.Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 8
            .Color = RGB(255, 255, 255)
        End With
        With .Chart.Axes(xlCategory).TickLabels.Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 8
            .Color = RGB(255, 255, 255)
        End With
        With .Chart.ChartTitle.Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 16
            .Color = RGB(0, 0, 255)
        End With
    End With

    With Sheets("ScrollingChart").ChartObjects.Add(410, 10, 400, 300)
        .Chart.SeriesCollection.NewSeries
        .Chart.Axes(1).CategoryType = 2
        .Chart.SeriesCollection(1).Values = "ScrollingChartData!B5:B5"
        .Chart.HasLegend = False

        .Chart.HasTitle = True
        .Chart.ChartTitle.Text = "DB CPU"

        .Chart.Axes(xlCategory, xlPrimary).HasTitle = True
        .Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = ""
        .Chart.Axes(xlValue, xlPrimary).HasTitle = True
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = ""

        .Chart.SeriesCollection(1).HasDataLabels = True
        .Chart.SeriesCollection(1).HasLeaderLines = True

        With .Chart.PlotArea.Border
            .ColorIndex = 16
            .Weight = xlThin
            .LineStyle = xlContinuous
        End With

        .Chart.PlotArea.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=2, Degree:=0.756847486076142
        .Chart.PlotArea.Fill.ForeColor.SchemeColor = 23
        .Chart.PlotArea.Fill.Visible = True
        With .Chart.PlotArea.Border
            .ColorIndex = 57
            .Weight = xlThin
            .LineStyle = xlContinuous
        End With

        .Chart.SeriesCollection(1).Fill.OneColorGradient Style:=msoGradientVertical, Variant:=4, Degree:=0.2
        .Chart.SeriesCollection(1).Fill.Visible = True
        .Chart.SeriesCollection(1).Fill.ForeColor.SchemeColor = 3

        .Chart.Axes(xlValue).MajorGridlines.Border.ColorIndex = 2
        With .Chart.SeriesCollection(1).DataLabels.Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 8
            .Color = RGB(255, 255, 255)
        End With
        With .Chart.Axes(xlCategory).TickLabels.Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 8
            .Color = RGB(255, 255, 255)
        End With
        With .Chart.ChartTitle.Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 16
            .Color = RGB(0, 0, 255)
        End With
    End With

    With Sheets("ScrollingChart").ChartObjects.Add(10, 320, 400, 300)
        .Chart.SeriesCollection.NewSeries
        .Chart.Axes(1).CategoryType = 2
        .Chart.SeriesCollection(1).Values = "ScrollingChartData!C5:C5"

        .Chart.HasLegend = False

        .Chart.HasTitle = True
        .Chart.ChartTitle.Text = "SQL Elapsed Time"

        .Chart.Axes(xlCategory, xlPrimary).HasTitle = True
        .Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = ""
        .Chart.Axes(xlValue, xlPrimary).HasTitle = True
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = ""

        .Chart.SeriesCollection(1).HasDataLabels = True
        .Chart.SeriesCollection(1).HasLeaderLines = True

        With .Chart.PlotArea.Border
            .ColorIndex = 16
            .Weight = xlThin
            .LineStyle = xlContinuous
        End With

        .Chart.PlotArea.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=2, Degree:=0.756847486076142
        .Chart.PlotArea.Fill.ForeColor.SchemeColor = 23
        .Chart.PlotArea.Fill.Visible = True
        With .Chart.PlotArea.Border
            .ColorIndex = 57
            .Weight = xlThin
            .LineStyle = xlContinuous
        End With

        .Chart.SeriesCollection(1).Fill.OneColorGradient Style:=msoGradientVertical, Variant:=4, Degree:=0.2
        .Chart.SeriesCollection(1).Fill.Visible = True
        .Chart.SeriesCollection(1).Fill.ForeColor.SchemeColor = 5

        .Chart.Axes(xlValue).MajorGridlines.Border.ColorIndex = 2
        With .Chart.SeriesCollection(1).DataLabels.Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 8
            .Color = RGB(255, 255, 255)
        End With
        With .Chart.Axes(xlCategory).TickLabels.Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 8
            .Color = RGB(255, 255, 255)
        End With
        With .Chart.ChartTitle.Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 16
            .Color = RGB(0, 0, 255)
        End With
    End With

    With Sheets("ScrollingChart").ChartObjects.Add(410, 320, 400, 300)
        .Chart.SeriesCollection.NewSeries
        .Chart.Axes(1).CategoryType = 2
        .Chart.SeriesCollection(1).Values = "ScrollingChartData!D5:D5"
        .Chart.HasLegend = False

        .Chart.HasTitle = True
        .Chart.ChartTitle.Text = "Parse Time"

        .Chart.Axes(xlCategory, xlPrimary).HasTitle = True
        .Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = ""
        .Chart.Axes(xlValue, xlPrimary).HasTitle = True
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = ""

        .Chart.SeriesCollection(1).HasDataLabels = True
        .Chart.SeriesCollection(1).HasLeaderLines = True

        With .Chart.PlotArea.Border
            .ColorIndex = 16
            .Weight = xlThin
            .LineStyle = xlContinuous
        End With

        .Chart.PlotArea.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=2, Degree:=0.756847486076142
        .Chart.PlotArea.Fill.ForeColor.SchemeColor = 23
        .Chart.PlotArea.Fill.Visible = True
        With .Chart.PlotArea.Border
            .ColorIndex = 57
            .Weight = xlThin
            .LineStyle = xlContinuous
        End With

        .Chart.SeriesCollection(1).Fill.OneColorGradient Style:=msoGradientVertical, Variant:=4, Degree:=0.2
        .Chart.SeriesCollection(1).Fill.Visible = True
        .Chart.SeriesCollection(1).Fill.ForeColor.SchemeColor = 6

        .Chart.Axes(xlValue).MajorGridlines.Border.ColorIndex = 2
        With .Chart.SeriesCollection(1).DataLabels.Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 8
            .Color = RGB(255, 255, 255)
        End With
        With .Chart.Axes(xlCategory).TickLabels.Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 8
            .Color = RGB(255, 255, 255)
        End With
        With .Chart.ChartTitle.Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 16
            .Color = RGB(0, 0, 255)
        End With
    End With

    'Make certain that the initial values are specified
    intStopScrollingChart = False
    lngLastRowScrollingChart = 3

    lngResult = mdlChartUpdater.ConnectDatabase

    If lngResult = True Then
        'If the connection attempt was successful, then start the updater
        UpdateChart
    End If
End Sub

Private Sub cmdStop_Click()
    intStopScrollingChart = True
End Sub

When the Start button on the worksheet is clicked, the above code deletes any charts on the ScrollingChart worksheet, creates 4 new charts, and then executes the ConnectDatabase and UpdateChart functions/procedures in the mdlChartUpdater module that was added in an earlier step.

Next, click the mdlChartUpdater module in the Visual Basic editor to switch to that code window – that is where the magic happens.  In the mdlChartUpdater module, add the following code:

Public intStopScrollingChart As Integer 'Used to indicate if the new rows are being added to the ScrollingChart sheet
Public lngLastRowScrollingChart As Long 'Used to keep track of the last row added to the Scrolling Chart tab

Option Explicit 'Forces all variables to be declared

Dim dbDatabase As New ADODB.Connection
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String

Dim intColumns As Integer
Dim strLastColumn As String

Public Function ConnectDatabase() As Integer
    Dim intResult As Integer

    On Error Resume Next

    If dbDatabase.State <> 1 Then
        'Connection to the database if closed
        strDatabase = "MyDB"
        strUserName = "MyUser"
        strPassword = "MyPassword"

        'Connect to the database
        'Oracle connection string
        dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"

        dbDatabase.ConnectionTimeout = 40
        dbDatabase.CursorLocation = adUseClient
        dbDatabase.Open

        If (dbDatabase.State <> 1) Or (Err <> 0) Then
            intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Excel Demo")

            ConnectDatabase = False
        Else
            ConnectDatabase = True
        End If
    Else
        ConnectDatabase = True
    End If
End Function

Public Sub UpdateChart()
    Dim sglChange As Single
    Dim strSQL As String

    Dim snpData As ADODB.Recordset

    If intStopScrollingChart = True Then
        Set snpData = Nothing
        Exit Sub
    End If

    On Error Resume Next

    Set snpData = New ADODB.Recordset

    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  STAT_NAME," & vbCrLf
    strSQL = strSQL & "  VALUE" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  V$SYS_TIME_MODEL" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  STAT_NAME IN ('DB time','DB CPU','sql execute elapsed time','parse time elapsed')" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT" & vbCrLf
    strSQL = strSQL & "  STAT_NAME," & vbCrLf
    strSQL = strSQL & "  VALUE" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  V$OSSTAT" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  STAT_NAME IN ('AVG_IDLE_TIME','AVG_BUSY_TIME','AVG_USER_TIME','AVG_SYS_TIME')" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT" & vbCrLf
    strSQL = strSQL & "  NAME STAT_NAME," & vbCrLf
    strSQL = strSQL & "  VALUE" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  V$SYSSTAT" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  NAME IN ('consistent gets','table scan rows gotten','user calls')"
    snpData.Open strSQL, dbDatabase

    If snpData.State = 1 Then
        lngLastRowScrollingChart = lngLastRowScrollingChart + 1

        'Recordset opened OK
        Do While Not (snpData.EOF)

            'Put the abolute values since startup starting in column 21, with the delta vales starting in column 1
            Select Case snpData("stat_name")
                Case "DB time"
                    Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 21).Value = snpData("value") / 1000000
                    If lngLastRowScrollingChart > 4 Then
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 1).Value = _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 21).Value - _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 21).Value
                    Else
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 1).Value = "DB Time"
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 1).Value = 0
                    End If
                Case "DB CPU"
                    Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 22).Value = snpData("value") / 1000000
                    If lngLastRowScrollingChart > 4 Then
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 2).Value = _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 22).Value - _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 22).Value
                    Else
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 2).Value = "DB CPU"
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 2).Value = 0
                    End If
                Case "sql execute elapsed time"
                    Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 23).Value = snpData("value") / 1000000
                    If lngLastRowScrollingChart > 4 Then
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 3).Value = _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 23).Value - _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 23).Value
                    Else
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 3).Value = "SQL Exec"
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 3).Value = 0
                    End If
                Case "parse time elapsed"
                    Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 24).Value = snpData("value") / 1000000
                    If lngLastRowScrollingChart > 4 Then
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 4).Value = _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 24).Value - _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 24).Value
                    Else
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 4).Value = "Parse Ela"
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 4).Value = 0
                    End If
                Case "consistent gets"
                    Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 25).Value = snpData("value")
                    If lngLastRowScrollingChart > 4 Then
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 5).Value = _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 25).Value - _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 25).Value
                    Else
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 5).Value = "Con Gets"
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 5).Value = 0
                    End If
                Case "table scan rows gotten"
                    Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 26).Value = snpData("value")
                    If lngLastRowScrollingChart > 4 Then
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 6).Value = _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 26).Value - _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 26).Value
                    Else
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 6).Value = "Tbl Scan Rows"
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 6).Value = 0
                    End If
                Case "user calls"
                    Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 27).Value = snpData("value")
                    If lngLastRowScrollingChart > 4 Then
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 7).Value = _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 27).Value - _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 27).Value
                    Else
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 7).Value = "User Calls"
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 7).Value = 0
                    End If
                Case "AVG_BUSY_TIME"
                    Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 28).Value = snpData("value") / 100
                    If lngLastRowScrollingChart > 4 Then
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 8).Value = _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 28).Value - _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 28).Value
                    Else
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 8).Value = "Avg Busy"
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 8).Value = 0
                    End If
                Case "AVG_IDLE_TIME"
                    Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 29).Value = snpData("value") / 100
                    If lngLastRowScrollingChart > 4 Then
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 9).Value = _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 29).Value - _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 29).Value
                    Else
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 9).Value = "Avg Idle"
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 9).Value = 0
                    End If
                Case "AVG_USER_TIME"
                    Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 30).Value = snpData("value") / 100
                    If lngLastRowScrollingChart > 4 Then
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 10).Value = _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 30).Value - _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 30).Value
                    Else
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 10).Value = "Avg User"
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 10).Value = 0
                    End If
                Case "AVG_SYS_TIME"
                    Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 31).Value = snpData("value") / 100
                    If lngLastRowScrollingChart > 4 Then
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 11).Value = _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 31).Value - _
                          Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 31).Value
                    Else
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart - 1, 11).Value = "Avg Sys"
                        Sheets("ScrollingChartData").Cells(lngLastRowScrollingChart, 11).Value = 0
                    End If
            End Select

            snpData.MoveNext
        Loop

        snpData.Close
    End If

    If lngLastRowScrollingChart > 4 Then

        'Update the source data locations for each chart - would not need to do this if we used named cell ranges
        ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Values = "ScrollingChartData!A" & _
          Format(IIf(lngLastRowScrollingChart - 19 > 5, lngLastRowScrollingChart - 19, 5)) & ":A" & Format(lngLastRowScrollingChart)
        ActiveSheet.ChartObjects(2).Chart.SeriesCollection(1).Values = "ScrollingChartData!B" & _
          Format(IIf(lngLastRowScrollingChart - 19 > 5, lngLastRowScrollingChart - 19, 5)) & ":B" & Format(lngLastRowScrollingChart)
        ActiveSheet.ChartObjects(3).Chart.SeriesCollection(1).Values = "ScrollingChartData!C" & _
          Format(IIf(lngLastRowScrollingChart - 19 > 5, lngLastRowScrollingChart - 19, 5)) & ":C" & Format(lngLastRowScrollingChart)
        ActiveSheet.ChartObjects(4).Chart.SeriesCollection(1).Values = "ScrollingChartData!D" & _
          Format(IIf(lngLastRowScrollingChart - 19 > 5, lngLastRowScrollingChart - 19, 5)) & ":D" & Format(lngLastRowScrollingChart)
    End If

    If intStopScrollingChart = False Then
        'Instruct Excel to execute the UpdateChart sub again in 60 seconds
        Application.OnTime DateAdd("s", 60, Now), "UpdateChart"
    End If
    Set snpData = Nothing
End Sub

Back in the ScrollingChartData worksheet, click the Start button.  Every 60 seconds (until the Stop button is clicked) the UpdateChart macro will re-execute itself, collecting the most recent statistics from the database.  After the macro has been running for a couple of minutes the worksheet might look something like this:

And the ScrollingChart tab might look something like this (zoomed at 75% – feel free to change the chart colors in the cmdStart code):

After 20+ minutes of logging, the ScrollingChartData worksheet might look like this:

And the ScollingChart worksheet might look like this:

There is certainly a lot that may be done to extend this example, but the above should give you the general idea of what needs to be done.








Follow

Get every new post delivered to your Inbox.

Join 142 other followers