View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
deko[_2_] deko[_2_] is offline
external usenet poster
 
Posts: 53
Default Excel not quitting - How to avoid global reference?

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).Colu mn
lr = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
.Cells(1).EntireRow.HorizontalAlignment = xlCenter
.Range("D2:D" & 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(xlD own).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.New Series
objSeries.Values = xlapp.Workbooks(strXlsFile).Worksheets _
(sn(i)).Range(xlapp.Workbooks(strXlsFile).Workshee ts _
(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).Worksheet s(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