msgraph 12.0 and inserting cells into datasheet (1 Viewer)

[brumster]

I think I've about exhausted my Googling on this issue and so am ready to do the inevitable and start a thread!

I'm programatically creating a graph on a form in Access 2007 using the msgraph 12.0 control and, in general, it's all working fine. On the form the user selects some criteria and can then add that data series to the graph - the idea being they can pick more than one value (it's graphing various performance metrics from servers) and "add" it into the graph as an additional line, so they can compare 2 or more data series over the same period of time.

The problem is, I cannot find any approach down at the Datasheet handling level in VBA that allows me to add another column, or range of cells, into the datasheet without it wiping the existing data that's in there.

I do the first "add" operation and it correctly adds in the cells into the datasheet, updates the graph, presto..

(link removed)
(FFS - I'd love to give you some links to pictures/screenshots but ofcourse being a n00b I can't)

If I now pull a new data set and try to programatically insert it into the datasheet object, there seems to be some sort of implicit "wipe", as the old data is cleared. The new cells appear in the correct position in the datasheet (ie. starting where the previous cells finished), but the ranges of the old data are all now cleared.

(link removed)

No, I'm not calling .clear ;-)

Here's the core bit of code...

Dim myObj As chart
Set myObj = Me.graph2.Object

myObj.Application.DataSheet.Activate
myObj.Application.DataSheet.Cells(1, 1).Value = "Time"

intSeries = intSeries + 1
myObj.Application.DataSheet.Cells(1, intSeries + 1).Value = "Series " & intSeries

Set db = CurrentDb()
Set rst = db.OpenRecordset(strChartSQL, DAO.RecordsetTypeEnum.dbOpenSnapshot)

Dim rowcount As Integer
rowcount = intStartAt

If Not (rst.BOF And rst.EOF) Then
    rst.MoveFirst
    
    '.ChartType = xlLineMarkers
    '.HasTitle = False
    '.Axes(Graph.XlAxisType.xlValue).HasTitle = True
    '.Axes(Graph.XlAxisType.xlCategory) = XlCategoryType.xlTimeScale
        
    ' Loop through all the rows in the database, adding them into the chart sheet
    Do While Not rst.EOF
        With myObj.Application.DataSheet
            .Cells(rowcount, 1).Value = rst("CalcTimestamp").Value
            .Cells(rowcount, 1 + intSeries).Value = rst("CalcMetric").Value
        End With
        rowcount = rowcount + 1
        rst.MoveNext
    Loop
    intStartAt = rowcount
Else
    'No records in the recordset - nothing to do/add...
End If
    
myObj.Application.Update
myObj.Application.Quit

Set myObj = Nothing
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

I can see via autocompletion and some old 2003 online MSDN documentation that there are various methods such as "insert", which I've played around with, but cannot get to do anything fruitful. The lack of substantial documentation from Microsoft isn't helping, to be honest.

Anyone any ideas?

 

[brumster]

Mmmm, ok, well for future reference of others...

Couldn't get this working, so instead took an approach of setting up a graph object bound to a temporary table of 6 columns (5 series plus the time series for the X axis), and just had my VBA procedure populate the temporary table and set the RecordSource on the graph object... thus avoiding any need to code against the chart's data sheet. Clearly it 'just don't work'... or it's so obtuse I gave up trying to figure it out