Excel remains in Memory

R

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
 
S

Stefan Hoffmann

hi Ray,

Ray_nl said:
Ive included 'the code I use below. Does anyone have any suggestions?
Click to expand...
Public Function TestExcel()
Dim objXLApp As New Excel.Application
Click to expand...
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 <--
 
R

Ray_nl

Stefan said:
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 <--
Click to expand...

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

Stefan Hoffmann

hi Ray,

Ray_nl said:
Tried that, didnt work eithert :(
Not sure what u mean by using internal data, could u explain please?
Click to expand...
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 <--
 
K

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.
 
K

Ken Snell \(MVP\)

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

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

Ray_nl via AccessMonster.com

Ken said:
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:
Click to expand...
[quoted text clipped - 95 lines]
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

reverse axis not working as before... 0
Charts in Multiple Sheet in Excel 1
Please Help! Need to add a Variable Range in my Excel Macro withPivot Tables 3
Code to Create Chart 0
How to change the row number in macro code? 1
Sorting in Excel from Access, Multiple Tab 5
Putting the series 1 and series 2 titles from a worksheet 0
Working with Excel in Access 1

Top