Nate Oliver's blog

Alright, so I made you set up the Analysis ToolPak, you're going to need it, but not for this - we shall revisit. I apologize for the delay.

What we're going to do tonight is explore an example of automating Excel to create a Chart Object, exactly where we want it (D1:H20), in a new instance of Excel. I'm testing this in Office 14, and having seen it as a go in previous versions, I say, Ghost Rider, the pattern is not full! Code me!

Sub foo()
Dim rs As ADODB.Recordset
Dim xlApp As Excel.Application, xlBook As Excel.Workbook
Dim xlChart As Excel.ChartObject
Dim i As Long

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add(1)
Set rs = New ADODB.Recordset

With rs
    ' Add field definitions...
    .Fields.Append "ID", adInteger
    .Fields.Append "Yadda", adInteger
    'Open her up
    .Open , , adOpenStatic, adLockOptimistic
    'Add new record
    .AddNew
    'Add Values
    .Fields(0).Value = 30: .Fields(1).Value = 300
    .AddNew
    'Add Values
    .Fields(0).Value = 69: .Fields(1).Value = 420
    'Update the record set
    .Update
    .MoveFirst
    Let i = .RecordCount
End With

With xlBook.Worksheets(1)
    .Range("A1:B1").Value = Array("Header1", "Header2")
    .Range("A2").CopyFromRecordset rs
    With .Range("D1") 'Anchor our Chart
        Set xlChart = .Parent.ChartObjects.Add( _
            .Left, .Top, .Width * 5, .RowHeight * 20)
    End With
End With

rs.Close:   Set rs = Nothing

With xlChart.Chart
    .ChartType = xlLineMarkers
    'Here we use our Recordcount
    .SetSourceData Source:=xlBook.Worksheets(1).Range("A1") _
        .Resize(i + 1, 2)
    .SeriesCollection(1).Name = "=""Bar"""
    With .SeriesCollection(2)
        .Name = "=""Foo"""
        .Formula = Replace$(.Formula, ",2)", ",1)")
    End With
    .Axes(xlCategory).Delete
End With

Set xlChart = Nothing

'Let's reset the chart, just to show you how to work with an existing chart
Set xlChart = xlBook.Worksheets(1).ChartObjects(1)
MsgBox xlChart.Name

xlApp.Visible = True
Set xlChart = Nothing:      Set xlBook = Nothing
Set xlApp = Nothing
End Sub

A few things to note. Office 14 wants you to freshly set a reference to ADO, as well as to Excel, this is Early-bound code. This is a trivial example, but not a bad one, although I did get lazy and instead of creating a table, I simply created a disconnected Recordset and it flows from there, i.e., there is no underlying data.

The rest should be good, not a sophisticated chart, but not a bad automation starting point. We shall revisit the ATP, soon.