Excel not quitting - How to avoid global reference?

[deko]

I'm using Access 2000 to create charts in Excel. I've read about the
problem Excel not quitting due to a global reference to the automated
application at http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/

So I've tried to reference objects explicitly. For example:

With xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
.[do stuff]
End With

But Excel still does not quit.

Sometimes I need to assign the return value of a method to an object - when
creating a chart or adding a series, for example:

Dim objChart as Object
Set objChart = xlapp.Workbooks(strXlsFile). _
Worksheets(sn(i)).ChartObjects.Add(Left:=100, Top:=24, _
Width:=650, Height:=500).Chart
With ObjChart
.[do stuff]
End with

Dim objSeries as Object
Set objSeries = objChart.SeriesCollection.NewSeries
With objSeries
.[do stuff]
End With

Is this why Excel is not quitting? Do either of the above two examples
constitute a global reference? Can this code be written so it does not
create a global reference? Could there be some other reason why Excel is
not quitting?

Thanks in advance.

 

[Nate Oliver]

Hello,

You haven't shown a few important details in your post, including:

1) How you have declared (scope-wise) xlApp. Is the variable declared within
the procedure or outside of the procedure?
2) Where you attempt to Quit the Application. I.e.,

xlApp.Quit

Yes, you certainly can create an Excel Application variable with scope
limited to the procedure, it would be declared similarly as you've declared:

Dim objChart as Object

Within the procedure.

Regards,
Nate Oliver

 

[deko]

You haven't shown a few important details in your post, including:

1) How you have declared (scope-wise) xlApp. Is the variable declared within
the procedure or outside of the procedure?
2) Where you attempt to Quit the Application. I.e.,

Thanks for the reply. Here's an abbreviated version of the function in
question. The function is called from a button on an Access form. As you
can see, everything is within scope of the function. The two objects I'm
wondering about are objChart and objSeries - each are set using the full
path "xlapp..."

Could either of those objects create a global reference? Should I look
elsewhere for the cause of the "Excel not quitting" problem?

Public Function CreateWorksheets() As Boolean
Dim xlapp As Excel.Application

For i = 1 To 50

With xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
lc = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
lr = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
.Cells(1).EntireRow.HorizontalAlignment = xlCenter
.Range("D2" & lr).Formula = "=STDEV(E2:AH2)"
.Columns.AutoFit
End With
Set objChart = xlapp.Workbooks(strXlsFile).Worksheets _
(sn(i)).ChartObjects.Add _
(Left:=100, Top:=24, Width:=650, Height:=500).Chart
With objChart
.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn(i)).Range("A1:B" & xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Range("B1").End(xlDown).Row), _
PlotBy:=xlColumns
.ChartType = xlLineMarkers
.Parent.Name = sn(i)
.HasLegend = False
.Axes(xlCategory, xlPrimary).HasTitle = False
End With
j = 3 'data begins in column 4
Do While j < lc - 1
j = j + 1
Set objSeries = xlapp.Workbooks(strXlsFile).Worksheets _
(sn(i)).ChartObjects(1).Chart.SeriesCollection.NewSeries
objSeries.Values = xlapp.Workbooks(strXlsFile).Worksheets _
(sn(i)).Range(xlapp.Workbooks(strXlsFile).Worksheets _
(sn(i)).Cells(2, j), xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Cells(lr, j))
Loop
k = 0
Do While k < objChart.SeriesCollection.Count
k = k + 1
With objChart.SeriesCollection(k)
.Border.ColorIndex = sm
.ErrorBar Direction:=xlY, Include:=xlBoth, _
Type:=xlCustom,
Amount:=xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("C2:C" & lr), _

MinusValues:=xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("C2:C" &
lr)
End With
Loop

Next i

CreateWorksheets = True
xlapp.Workbooks(strXlsFile).Save
xlapp.Workbooks(strXlsFile).Close
Set objSeries = Nothing
Set objChart = Nothing
Set xlapp = Nothing
xlapp.Quit

Exit Function

 

[deko]

Forgot to include how I am instantiating the xlapp object...


Public Function CreateWorksheets() As Boolean
On Error GoTo HandleErr
Dim xlapp As Excel.Application
Set xlapp = GetObject(, "Excel.Application")
'gets new instance on error if no current instance exists

For i = 1 To 50

With xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
lc = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
lr = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
.Cells(1).EntireRow.HorizontalAlignment = xlCenter
.Range("D2" & lr).Formula = "=STDEV(E2:AH2)"
.Columns.AutoFit
End With
Set objChart = xlapp.Workbooks(strXlsFile).Worksheets _
(sn(i)).ChartObjects.Add _
(Left:=100, Top:=24, Width:=650, Height:=500).Chart
With objChart
.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn(i)).Range("A1:B" & xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Range("B1").End(xlDown).Row), _
PlotBy:=xlColumns
.ChartType = xlLineMarkers
.Parent.Name = sn(i)
.HasLegend = False
.Axes(xlCategory, xlPrimary).HasTitle = False
End With
j = 3 'data begins in column 4
Do While j < lc - 1
j = j + 1
Set objSeries = xlapp.Workbooks(strXlsFile).Worksheets _
(sn(i)).ChartObjects(1).Chart.SeriesCollection.NewSeries
objSeries.Values = xlapp.Workbooks(strXlsFile).Worksheets _
(sn(i)).Range(xlapp.Workbooks(strXlsFile).Worksheets _
(sn(i)).Cells(2, j), xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Cells(lr, j))
Loop
k = 0
Do While k < objChart.SeriesCollection.Count
k = k + 1
With objChart.SeriesCollection(k)
.Border.ColorIndex = sm
.ErrorBar Direction:=xlY, Include:=xlBoth, _
Type:=xlCustom,
Amount:=xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("C2:C" & lr), _

MinusValues:=xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("C2:C" &
lr)
End With
Loop

Next i

CreateWorksheets = True
xlapp.Workbooks(strXlsFile).Save
xlapp.Workbooks(strXlsFile).Close
Set objSeries = Nothing
Set objChart = Nothing
Set xlapp = Nothing
xlapp.Quit

HandleErr:
Select Case Err.Number
Case 429
Set xlapp = CreateObject("Excel.Application")
Resume Next
[code omitted]

Exit Function

 

[Nate Oliver]

Hello again,

Why not use CreateObject in all cases? Versus hijacking and quitting an
existing Excel instance.

In any case, for starters, try flipping:

Set xlapp = Nothing
xlapp.Quit

To

xlapp.Quit
Set xlapp = Nothing

You're trying to control an object variable which you have terminated from
memory.

Regards,
Nate Oliver

 

[deko]

Why not use CreateObject in all cases? Versus hijacking and quitting an

existing Excel instance.

In any case, for starters, try flipping:

Set xlapp = Nothing
xlapp.Quit

To

xlapp.Quit
Set xlapp = Nothing

You're trying to control an object variable which you have terminated from
memory.

Great comments. I'll try both suggestions.

Thanks for the help.