Displaying a chart in a form

[Michael Hudston]

I have the following code which creates a Chart from the data in a query,
when a form is opened. How can I then display this chart in the form, so I
can close Excel.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Err

' Dimension arrays for recordset & excel etc

Dim inStat As ADODB.Recordset
Dim objExcel As Excel.Application
Dim objSheet As Excel.Worksheet
Dim objChart As Excel.Chart

Dim fld As ADODB.Field
Dim intCol As Integer
Dim intRow As Integer

' Populate the recordset.

Set inStat = New ADODB.Recordset
inStat.Open "BE_Status_Updates Query", CurrentProject.Connection

' Launch Excel and Create WorkSheet

Set objExcel = New Excel.Application
objExcel.Workbooks.Add
Set objSheet = objExcel.ActiveSheet

objExcel.Visible = True

' Create the Headings

For intCol = 0 To 1
Set fld = inStat.Fields(intCol)
objSheet.Cells(1, intCol + 2) = fld.Name
Next intCol

For intCol = 2 To inStat.Fields.Count
Set fld = inStat.Fields(inStat.Fields.Count - intCol + 1)
objSheet.Cells(1, inStat.Fields.Count - intCol + 2) = fld.Name
Next intCol

' Transfer the Data

intRow = 2
Do Until inStat.EOF
For intCol = 0 To inStat.Fields.Count - 1
objSheet.Cells(intRow, intCol + 1) = inStat.Fields(intCol).Value
Next intCol
inStat.MoveNext
intRow = intRow + 1
Loop

' Add New Chart

objExcel.Charts.Add
Set objChart = objExcel.ActiveChart

' Set up the Chart

objChart.ChartType = xlAreaStacked
objChart.SetSourceData Source:=objSheet.Range("B1:H" & CStr(intRow -
1)), Plotby:=xlColumns
objChart.Location xlLocationAsNewSheet
objChart.HasTitle = True
objChart.ChartTitle.Characters.Text = "Incident Status by Date"
objChart.Axes(xlCategory).MajorUnit = 7
objChart.ChartArea.Copy

objExcel.Quit

Form_Exit:
Exit Sub

Form_Err:
MsgBox Error$
Resume Form_Exit

End Sub

 

[Michael Hudston]

Anyone?

 

[Rick Brandt]

Anyone?

Any reason you don't just use the chart capabilities in Access rather
than trying to automate Excel? It is the same charting engine.