Generating Excel Chart From Access Form

[Guest]

Generating and Excel2000 Chart from and MSAccess2000 Form. When finished
viewing the chart, I can not close Excel properly. So that when I change the
MSAccess Form data, I can no longer generate the Excel Chart...there is an
instance of Excel remaining open in the background preventing the reuse of
the MSAccess Form to generate a new Excel Chart. When I try to shut down the
PC, there are multiple back ground instances of Excel that have to be shut
down before the PC can be shut down. I am going back to the MSAccess Form
and using an MSAccess command to close Excel.

Private Sub cmdCreateGraph_Click()
On Error GoTo cmdCreateGraph_Err
Dim rstData As ADODB.Recordset
Dim rstCount As ADODB.Recordset
Dim fld As ADODB.Field
Dim rng As Excel.Range
Dim objWS As Excel.Worksheet
Dim intRowCount As Integer
Dim intColCount As Integer

'Display Hourglass
'DoCmd.Hourglass True
Set rstData = New ADODB.Recordset
rstData.ActiveConnection = CurrentProject.Connection
Set rstCount = New ADODB.Recordset
rstCount.ActiveConnection = CurrentProject.Connection

'Attempt to create Recordset and launch Excel
If CreateRecordset(rstData, rstCount, "qrySalesByCountry") Then
If CreateExcelObj() Then
gobjExcel.Workbooks.Add
Set objWS = gobjExcel.ActiveSheet
intRowCount = 1
intColCount = 1
'Loop though Fields collection using field names
'as column headings
For Each fld In rstData.Fields
If fld.Type <> adLongVarBinary Then
objWS.Cells(1, intColCount).Value = fld.Name
intColCount = intColCount + 1
End If
Next fld
'Send Recordset to Excel
objWS.Range("A1").CopyFromRecordset rstData, 500


'Format Data
With gobjExcel
.Columns("A:B").Select
.Columns("A:B").EntireColumn.AutoFit
.Range("A1").Select
.ActiveCell.CurrentRegion.Select
Set rng = .Selection
.Selection.NumberFormat = "$#,##0.00"
'Add a Chart Object
.ActiveSheet.ChartObjects.Add(135.75, 14.25, 607.75,
301).Select

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData
Source:=Sheets("Sheet1").Range("A1:B21"), PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Corrosion Rate"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"Corrosion Rate (mpy)"
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False '''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Make Excel Visible
.Visible = True
End With
Else
MsgBox "Excel Not Successfully Launched"
End If
Else
MsgBox "Too Many Records to Send to Excel"
End If
DoCmd.Hourglass False

cmdCreateGraph_Exit:
Set rstData = Nothing
Set rstCount = Nothing
Set fld = Nothing
Set rng = Nothing
Set objWS = Nothing
DoCmd.Hourglass False
Exit Sub

cmdCreateGraph_Err:
MsgBox "Error # " & Err.Number & ": " & Err.Description
Resume cmdCreateGraph_Exit
End Sub

'Close Excel from the MSAccess Form using this cmd_click
Private Sub Command3_Click()
CloseExcel
End Sub

 

[John Nurick]

Hi Terry,

You haven't shown what your CreateExcelObj() function does, but it from
your description of its behaviour it creates a new instance of Excel and
points gobjExcel at it - thus leaving any previous instances isolated
and unloved in the background.

To avoid this, use something like this air code instead:

Function GetExcelApplication() As Object
Dim objXL As Excel.Application
On Error Resume Next
'Try to grab an existing instance of Excel
Set objXL = GetObject(,"Excel.Application")
If objXL Is Nothing Then
'None found, create a new one
Set objXL = CreateObject("Excel.Application")
End If
If objXL Is Nothing Then
MsgBox "Excel Not Successfully Launched"
Set GetExcelApplication = Nothing
Else
Set GetExcelApplication = objXL
End If
Set objXL = Nothing
End Function


and in your graphing procedure

Dim objXL As Excel.Application
...
Set objXL = GetExcelApplication()
If obXL Is Nothing Then
MsgBox "Couldn't launch Excel"
Else
'go to work on objXL instead of gobjExcel

End If

This should avoid the creation of multiple hidden instances of Excel.
But if GetExcelApplication() creates a new instance and
cmdCreateGraph_Click()then calls its error handler before reaching the
line
.Visible = True
you will get one invisible instance left behind. To avoid this, use
something like this in the error handler:

If Not objXL Is Nothing Then
objXL.Visible = True
End If

thus making it visible for the user to dispose of later.