Access to Excel Chat automation problem

[OC2]

Hi,

I have written an access program, which will open Excel and create a chart. My problem though is, upon quitting excel after creating the chart. Excel stays in the windows memory. Only when I either quit access or generate an error does it remove itself. This cause a problem because, I have designed my code to only open Excel if it isnt open, therefore to prevent duplicate copies or read onlys.

Could someone pls explain how I can remove Excel from WindowsXP memory when I exit Excel after creating this chart. It is definetely to do with Access. If I automate reports, this doesnt happen. It only happens when I create a chart.

For your reference here is the code ive used, it does not quit or save as I want to solve this first.

thx

Dim objXL As New Excel.Application
Dim XLObj As Object
Dim Analysis As String
Dim test As String

'''''''''''''''''''''''''''''''''''''''''''''''''''''
Analysis = Me!text
'test = Me!test3


'''''''''''''''''''''''''''''''''''''''''''''''''''''

On Error Resume Next

Set XLObj = GetObject(, "Excel.application&quot
If Err.Number = 0 Then
MsgBox ("Excel is Open, please close it and try again&quot, vbOKOnly
GoTo On_close
End If

Set objXL = CreateObject("Excel.Application&quot
objXL.Visible = True
With objXL
.Workbooks.Open Filename:=CLT_LOCATION
End With
objXL.ActiveWorkbook.Sheets(Analysis).Select
objXL.ActiveWorkbook.ActiveSheet.Cells(10, 15) = "1"
objXL.ActiveWorkbook.ActiveSheet.Cells(10, 16) = "2"
objXL.ActiveWorkbook.ActiveSheet.Cells(10, 17) = "3"
objXL.ActiveWorkbook.ActiveSheet.Cells(10, 18) = "4"
objXL.ActiveWorkbook.Charts.Add
objXL.ActiveChart.ChartType = xlColumnStacked100
objXL.ActiveChart.SetSourceData Sheets(Analysis).Range("O10:R23&quot
objXL.ActiveChart.PlotBy = xlRows
objXL.ActiveChart.SeriesCollection(1).XValues = "=test!R9C15:R9C18"
objXL.ActiveChart.SeriesCollection(1).Name = "=test!R10C4"
objXL.ActiveChart.SeriesCollection(2).Name = "=test!R11C4"
objXL.ActiveChart.SeriesCollection(3).Name = "=test!R12C4"
objXL.ActiveChart.SeriesCollection(4).Name = "=test!R13C4"
objXL.ActiveChart.SeriesCollection(5).Name = "=test!R14C4"
objXL.ActiveChart.SeriesCollection(6).Name = "=test!R15C4"
objXL.ActiveChart.SeriesCollection(7).Name = "=test!R16C4"
objXL.ActiveChart.SeriesCollection(8).Name = "=test!R17C4"
objXL.ActiveChart.SeriesCollection(9).Name = "=test!R18C4"
objXL.ActiveChart.SeriesCollection(10).Name = "=test!R19C4"
objXL.ActiveChart.SeriesCollection(11).Name = "=test!R20C4"
objXL.ActiveChart.SeriesCollection(12).Name = "=test!R21C4"
objXL.ActiveChart.SeriesCollection(13).Name = "=test!R22C4"
objXL.ActiveChart.SeriesCollection(14).Name = "=test!R23C4"
objXL.ActiveChart.Location (xlLocationAsNewSheet), "test"
With objXL.ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.charactors.text = "Category"
End With
objXL.ActiveChart.ApplyDataLabels (xlDataLabelsShowValue)


On_close:
Set XLObj = Nothing
Resume Next

End Sub

 

[rekclaw]

I noticed that you set the XLObj object to nothing to clean up that memory. Shouldn't you also be seting the objXL object to nothing as well?

 

[OC2]

After debbugging line by line I discovered that this line was the cause of the problem

objXL.ActiveChart.SetSourceData Sheets(Analysis).Range("O10:R23&quot

The reason why Excel is staying in the memory is because of this part,

Sheets(Analysis).Range

I dont know why it worked, but it did, and for some reason was causing Excel to stay in the windows memory,

The code should have been

objXL.Sheets(Analysis).Range

after applying this the code worked a charm. Its annoying when something so obvious takes 1 and a half days of debugging to work out lol.


The line should have read