Excel remains in Memory

[Ray_nl]

Hi,

I've got a problem with Access and Excel.
The situation: from Access2000 i create a Pivot-table in Excel2000. This
works like a charm. However I need to close Access before creating a 2nd
Pivot because an Excel-process remains in memory.

I think I've eliminated all implicit references but it doesnt seem to close
the excel.exe process when I quit Excel.
Ive included 'the code I use below. Does anyone have any suggestions?

Thnx in advance!

Public Function TestExcel()
Dim objXLApp As New Excel.Application
Dim objXLBook As Excel.Workbook
Dim strDatabase As String
Dim strSavename As String

objXLApp.Workbooks.Add
Set objXLBook = objXLApp.ActiveWorkbook

strDatabase = CurrentDb.Name

With objXLApp.ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & strDatabase &
";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = "SELECT ObjectType, obj_code, Jaar, Score FROM
qScore_Object_Code_Jaar_Gemiddeld ORDER BY ObjectType"
.CreatePivotTable TableDestination:="", TableName:="Draaitabel1"
End With
objXLBook.Sheets(1).Select
objXLApp.ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells
(3, 1)
objXLApp.ActiveSheet.Cells(3, 1).Select
objXLApp.ActiveSheet.PivotTables("Draaitabel1").SmallGrid = False
objXLApp.Charts.Add
objXLApp.ActiveChart.SetSourceData Source:=Sheets("Blad4").Range("A3")
objXLApp.ActiveChart.Location Where:=xlLocationAsNewSheet

With objXLApp.ActiveChart.PivotLayout.PivotFields("Score")
.Orientation = xlDataField
.Position = 1
End With

With objXLApp.ActiveChart.PivotLayout.PivotFields("obj_code")
.Orientation = xlRowField
.Position = 1
End With

With objXLApp.ActiveChart.PivotLayout.PivotFields("Jaar")
.Orientation = xlColumnField
.Position = 1
End With


objXLApp.ActiveChart.ChartType = xlLineMarkers
objXLApp.ActiveChart.Location Where:=xlLocationAsNewSheet
With objXLApp.ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

With objXLApp.ActiveChart.PivotLayout.PivotFields("ObjectType")
.Orientation = xlPageField
.Position = 1
End With
objXLApp.ActiveChart.PivotLayout.PivotFields("ObjectType").CurrentPage =
"PART145"

strSavename = CurrentProject.Path & "\Pivot\Pivot " & Replace(Now(), ":",
"") & ".xls"
objXLBook.SaveAs strSavename

MsgBox "The Pivotchart has been created and saved. " & vbCrLf & _
"It has been saved to: " & vbCrLf & strSavename, vbInformation, "Save
Information"

objXLBook.Close
objXLApp.Application.Quit

Set objXLBook = Nothing
Set objXLApp = Nothing

End Function

 

[Alex Dybenko]

Hi,
try to declare as:

Dim objXLApp As Excel.Application

and then initiate it as:

set objXLApp =createobject("Excel.Application")


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Ray_nl]

Hi,
try to declare as:

Dim objXLApp As Excel.Application

and then initiate it as:

set objXLApp =createobject("Excel.Application")

Hi,

[quoted text clipped - 88 lines]

End Function

Hi Alex,

thanks but that didn't work as well. Its driving me mad
Any other suggestions?

 

[Stefan Hoffmann]

hi Ray,

Ive included 'the code I use below. Does anyone have any suggestions?

Public Function TestExcel()
Dim objXLApp As New Excel.Application

As Alex already wrote:

Drop the New keyword and create the instance manually, e.g.

Dim objXLApp As Excel.Application

Set objXLApp = New Excel.Application

Reorder the clean up:

objXLBook.Close
Set objXLBook = Nothing

objXLApp.Quit
Set objXLApp = Nothing

And try using internal data for creating the pivot. Depending on your
data use the ap.Range().CopyFromRecordset method.


mfG
--> stefan <--

 

[Ray_nl]

hi Ray,


As Alex already wrote:

Drop the New keyword and create the instance manually, e.g.

Dim objXLApp As Excel.Application

Set objXLApp = New Excel.Application

Reorder the clean up:

objXLBook.Close
Set objXLBook = Nothing

objXLApp.Quit
Set objXLApp = Nothing

And try using internal data for creating the pivot. Depending on your
data use the ap.Range().CopyFromRecordset method.

mfG
--> stefan <--

Tried that, didnt work eithert
Not sure what u mean by using internal data, could u explain please?

 

[Stefan Hoffmann]

hi Ray,

Tried that, didnt work eithert
Not sure what u mean by using internal data, could u explain please?

You are opening a data connection in Excel to Access. I'm not sure, if
this is cause.

An Excel instance is normally left open, when

a) an error occured and you lost the connection to it
b) the New keyword is used in a Dim statement.

b) is a bug, test the following:

Public Sub TestDimNew

Dim ap As New Excel.Application

ap.Quit

MsgBox "Closed Excel"

Set ap = Nothing

ap.Visible = True

End Sub



mfG
--> stefan <--

 

[Ken Snell \(MVP\)]

You have a reference to EXCEL objects that are not fully qualified in this
line:

objXLApp.ActiveChart.SetSourceData Source:=Sheets("Blad4").Range("A3")


See the Sheets reference? Qualify it with the object in which the Sheets
collection is located.

 

[Ken Snell \(MVP\)]

I also note an unqualified reference to ActiveSheet in this step:

objXLApp.ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)

 

[Ray_nl via AccessMonster.com]

I also note an unqualified reference to ActiveSheet in this step:

objXLApp.ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)

You have a reference to EXCEL objects that are not fully qualified in this
line:

[quoted text clipped - 95 lines]

Thanks Ken!
That and some adjusted code tackled the problem. it (finally) works

Following is the code, in case anyone else bumps into the same problem.
Thanks all for your help!

Public Function TestExcel()
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim strDatabase As String
Dim strSavename As String

Set objXLApp = New Excel.Application
objXLApp.Visible = True

If objXLApp.Workbooks.Count > 0 Then
For Each objXLBook In objXLApp.Workbooks
objXLBook.Close
Next
End If

Set objXLBook = objXLApp.Workbooks.Add

strDatabase = CurrentDb.Name

With objXLApp.ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & strDatabase &
";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = "SELECT ObjectType, obj_code, Jaar, Score FROM
qScore_Object_Code_Jaar_Gemiddeld ORDER BY ObjectType"
.CreatePivotTable TableDestination:="", TableName:="Draaitabel1"
End With
objXLBook.Sheets(1).Select
objXLApp.ActiveSheet.PivotTableWizard TableDestination:=objXLApp.
ActiveSheet.Cells(3, 1)
objXLApp.ActiveSheet.Cells(3, 1).Select
objXLApp.ActiveSheet.PivotTables("Draaitabel1").SmallGrid = False
objXLApp.Charts.Add
objXLApp.ActiveChart.SetSourceData Source:=objXLApp.Sheets(2).Range("A3")
objXLApp.ActiveChart.Location Where:=xlLocationAsNewSheet

With objXLApp.ActiveChart.PivotLayout.PivotFields("Score")
.Orientation = xlDataField
.Position = 1
End With

With objXLApp.ActiveChart.PivotLayout.PivotFields("obj_code")
.Orientation = xlRowField
.Position = 1
End With

With objXLApp.ActiveChart.PivotLayout.PivotFields("Jaar")
.Orientation = xlColumnField
.Position = 1
End With


objXLApp.ActiveChart.ChartType = xlLineMarkers
objXLApp.ActiveChart.Location Where:=xlLocationAsNewSheet
With objXLApp.ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

With objXLApp.ActiveChart.PivotLayout.PivotFields("ObjectType")
.Orientation = xlPageField
.Position = 1
End With
objXLApp.ActiveChart.PivotLayout.PivotFields("ObjectType").CurrentPage =
"PART145"

strSavename = CurrentProject.Path & "\Pivot\Pivot " & Replace(Now(), ":",
"") & ".xls"
objXLBook.SaveAs strSavename

MsgBox "The Pivotchart has been created and saved. " & vbCrLf & _
"It has been saved to: " & vbCrLf & strSavename, vbInformation, "Save
Information"

objXLBook.Close
objXLApp.Application.Quit

Set objXLBook = Nothing
Set objXLApp = Nothing

End Function