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.
Charles Hooper's Oracle Notes 
Hi,
great info , but is that supposed to work with Excel 2003 ?
Is scrolling charts Excel 2007 feature only ?
Regards.
Greg
The gradiant shading of the chart objects is new in Excel 2007. The example will work with Excel 2003 also, but you will need to make a couple of adjustments in the cmdStart_Click code.
To see what options are permitted in Excel 2003, tell Excel 2003 to start recording a macro. Next, create a chart with as many of the formatting options specified as you would like to see in the final result. Once the chart is created, stop the macro recording – Excel should have written much of the code needed to reproduce the chart in one of the code modules. For example (this is just a basic chart):
Sub Macro1() Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:A6"), PlotBy:= _ xlColumns ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Test" .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End SubThank You for an explanation.
Regards.
Greg
[…] 13-How to visualize live Oracle Performance data in scrolling graphs with excel? Charles Hooper- Excel – Scrolling Oracle Performance Charts […]
[…] some of the examples Charles Hooper has posted on connecting to and monitoring Oracle, for example https://hoopercharles.wordpress.com/2010/01/20/excel-scrolling-oracle-performance-charts/ I kept getting the error “Provider not found” Now what kind of trace info is there for […]
[…] Auto-scrolling charts in Excel: https://hoopercharles.wordpress.com/2010/01/20/excel-scrolling-oracle-performance-charts/ Shows how to create a UserForm in Excel and display that UserForm using a VBS script: […]
[…] https://hoopercharles.wordpress.com/2010/01/20/excel-scrolling-oracle-performance-charts/ […]