xlApp.Quit - Doesn't Close Instance.... Any ideas?

[MikeZz]

Hi,
I've been having some memory problems and so I tried a little test.

According to this group and other resources, the routine below should open a
new file in a new xlApp, then upon closing, quiting, setting = nothing,
should remove it from the Task Manager.

However, it does not get rid of it. I can't see it in my windows toolbar as
an open file but it shows up in Task Manager. The other odd thing is that I
can re-run it multiple times (opening the same file) and sometimes and get
totally different Memory usage... one time it's 28MB, another it's only 15MB.

Thanks for any thoughts,
MikeZz


Sub QuickNewAppTest()
Dim MasterFile
Dim f, c, r
Dim Master As Workbook
Dim masterSht As Worksheet
Dim FileString

FileString = "S:\ATCCommon\SALES\gm\Contracts\00New\Excel\C-0C5D0-000-011.xls"
Dim xlApp As New Excel.Application 'ADDED FOR MEMORY

Application.ScreenUpdating = False
xlApp.Application.Visible = True 'ADDED FOR MEMORY

xlApp.Workbooks.Open (FileString) 'Focus is now on the workbook 'ADDED FOR
MEMORY

Set Master = xlApp.ActiveWorkbook
Set masterSht = xlApp.ActiveSheet
MasterFile = Master.Name

Master.Close SaveChanges:=False

Set masterSht = Nothing
Set Master = Nothing
xlApp.Quit
Set xlApp = Nothing 'ADDED FOR MEMORY
Application.ScreenUpdating = True

End Sub

 

[Jim Thomlinson]

I can not address your memory usage as we will not be opening the same file.
As for creating a new instance of XL and then destroying that instance the
code you posted works on my machine. Here is what I ran and it created an
destroyed an instance of XL in the task manager... Th eonly changes was
removing non essential declarations and explicitly defining the data types...

Sub QuickNewAppTest()
Dim MasterFile As String
Dim Master As Workbook
Dim masterSht As Worksheet
Dim FileString As String

FileString = "C:\Test.xls"
Dim xlApp As New Excel.Application 'ADDED FOR MEMORY

Application.ScreenUpdating = False
xlApp.Application.Visible = True 'ADDED FOR MEMORY

xlApp.Workbooks.Open (FileString) 'Focus is now on the workbook 'ADDED FOR
MEMORY

Set Master = xlApp.ActiveWorkbook
Set masterSht = xlApp.ActiveSheet
MasterFile = Master.Name

Master.Close SaveChanges:=False

Set masterSht = Nothing
Set Master = Nothing
xlApp.Quit
Set xlApp = Nothing 'ADDED FOR MEMORY
Application.ScreenUpdating = True

End Sub

 

[MikeZz]

Thanks Jim,
I'm running Office 2003.
Is it possible that there is either a VBA Option/Preference or maybe a
library that I don't have loaded which is required to run that command?

It's strange but I think when I ran code similar to this sequence at home
(Also 2003), it closed the instance and at work, it didn't. And if I recall,
it was the same Excel File so the same exact VBA Routine.

Baffeling, just Baffeling to me.

Thanks again for the help,
MikeZz

 

[Jim Cone]

Which office application are you using to run your code?
--
Jim Cone
Portland, Oregon USA



"MikeZz"
wrote in message
Hi,
I've been having some memory problems and so I tried a little test.
According to this group and other resources, the routine below should open a
new file in a new xlApp, then upon closing, quiting, setting = nothing,
should remove it from the Task Manager.

However, it does not get rid of it. I can't see it in my windows toolbar as
an open file but it shows up in Task Manager. The other odd thing is that I
can re-run it multiple times (opening the same file) and sometimes and get
totally different Memory usage... one time it's 28MB, another it's only 15MB.
Thanks for any thoughts,
MikeZz

 

[MikeZz]

I'm using Excel 2003 with SP3 installed.

 

[Jim Cone]

Why then are you opening another instance of Excel?
What happens when you run your code in the existing instance of Excel?

As far as the new instance of Excel remaining open -
a common cause is the creation of "orphan" references that prevent Excel from closing.
Those can result from the use of unqualified references such as ActiveSheet or Range ("A1").
--
Jim Cone
Portland, Oregon USA



"MikeZz"
wrote in message
I'm using Excel 2003 with SP3 installed.

 

[MikeZz]

Hi Jim,
I am trying this method because I need to import data from several hundred
small excel files to process a summary of the data. The files are random in
nature with data scattered throughout. When I load the data using the
current instance, it doesn't seem to let go of the memory and by the 300th
file, it takes about 60x as long just to read the data into an array... and
the files are pretty small. There may be some unqualified references in the
original code but were talking about 50 different routines and functions... a
needle in a haystack. This method has shown some promise because the routine
to read in the file data is isolated.

Do I have Orphan References in my Example?
As you can see from my example code (which is a direct paste from VBA), I
don't have an references that I would consider to be Orphan.... it's about as
simple as possible. I could even shrink it down to just Open & Close the
file and the Instance still remains.

On a side note, Pardon my lack of knowledge of orphan references...
If I have something that says ActiveSheet, can I just replace it with
something like this:

set MySheet = ActiveSheet
x = MySheet.Range("A1").value
set MySheet = Nothing

as oposed to just: x = ActiveSheet.Range("A1").Value

Thanks for the help,
MikeZz

Why then are you opening another instance of Excel?
What happens when you run your code in the existing instance of Excel?

As far as the new instance of Excel remaining open -
a common cause is the creation of "orphan" references that prevent Excel from closing.
Those can result from the use of unqualified references such as ActiveSheet or Range ("A1").
--
Jim Cone
Portland, Oregon USA



"MikeZz"
wrote in message
I'm using Excel 2003 with SP3 installed.

 

[Jim Cone]

It is difficult to critique imaginary code...
Do not use "New" in a variable declaration - see Chip Pearson's website if
you want the why of that.
ActiveSheet is an unqualified reference. Do not use it without specifying
the application it belong to. If fact I would not use it at all.
Suggested format for automation code...

Dim xlApp as Excel.Application
Dim WB as Excel.Workbook
Dim WS as Excel.Worksheet
Dim bigRange as Excel.Range
Set xlApp = New Excel.Application

Start of loop
Set WB = xlApp.Open(FileName)
Set WS = WB.Worksheets(1)

Do stuff using WS.Range("xxxx") or Set a reference to the range...
Set bigRange = WS.Range("xxxx")
(do not use "Selection" or "ActiveCell" or anything similar)
(do not use the "With" construct - use an object reference)

Set the worksheet and range references to Nothing
Close the Workbook and specify whether to save it or not.
Set the Workbook reference to Nothing
End of Loop

After looping thru all of the files, quit the application and set it to nothing.
'--
Jim Cone
Portland, Oregon USA



"MikeZz"
wrote in message
Hi Jim,
I am trying this method because I need to import data from several hundred
small excel files to process a summary of the data. The files are random in
nature with data scattered throughout. When I load the data using the
current instance, it doesn't seem to let go of the memory and by the 300th
file, it takes about 60x as long just to read the data into an array... and
the files are pretty small. There may be some unqualified references in the
original code but were talking about 50 different routines and functions... a
needle in a haystack. This method has shown some promise because the routine
to read in the file data is isolated.

Do I have Orphan References in my Example?
As you can see from my example code (which is a direct paste from VBA), I
don't have an references that I would consider to be Orphan.... it's about as
simple as possible. I could even shrink it down to just Open & Close the
file and the Instance still remains.

On a side note, Pardon my lack of knowledge of orphan references...
If I have something that says ActiveSheet, can I just replace it with
something like this:

set MySheet = ActiveSheet
x = MySheet.Range("A1").value
set MySheet = Nothing

as oposed to just: x = ActiveSheet.Range("A1").Value

Thanks for the help,
MikeZz

Why then are you opening another instance of Excel?
What happens when you run your code in the existing instance of Excel?

As far as the new instance of Excel remaining open -
a common cause is the creation of "orphan" references that prevent Excel from closing.
Those can result from the use of unqualified references such as ActiveSheet or Range ("A1").
--
Jim Cone
Portland, Oregon USA



"MikeZz"
wrote in message
I'm using Excel 2003 with SP3 installed.

 

[MikeZz]

Jim,
Tried your code verbatim and got the following error:

Object doesn't support this property or method (Error 438)
Line causing the error:
Set WB = xlApp.Open("S:\00New\Excel\C-16LM0-01G-000.xls")

Ideas?

It is difficult to critique imaginary code...
Do not use "New" in a variable declaration - see Chip Pearson's website if
you want the why of that.
ActiveSheet is an unqualified reference. Do not use it without specifying
the application it belong to. If fact I would not use it at all.
Suggested format for automation code...

Dim xlApp as Excel.Application
Dim WB as Excel.Workbook
Dim WS as Excel.Worksheet
Dim bigRange as Excel.Range
Set xlApp = New Excel.Application

Start of loop
Set WB = xlApp.Open(FileName)
Set WS = WB.Worksheets(1)

Do stuff using WS.Range("xxxx") or Set a reference to the range...
Set bigRange = WS.Range("xxxx")
(do not use "Selection" or "ActiveCell" or anything similar)
(do not use the "With" construct - use an object reference)

Set the worksheet and range references to Nothing
Close the Workbook and specify whether to save it or not.
Set the Workbook reference to Nothing
End of Loop

After looping thru all of the files, quit the application and set it to nothing.
'--
Jim Cone
Portland, Oregon USA



"MikeZz"
wrote in message
Hi Jim,
I am trying this method because I need to import data from several hundred
small excel files to process a summary of the data. The files are random in
nature with data scattered throughout. When I load the data using the
current instance, it doesn't seem to let go of the memory and by the 300th
file, it takes about 60x as long just to read the data into an array... and
the files are pretty small. There may be some unqualified references in the
original code but were talking about 50 different routines and functions... a
needle in a haystack. This method has shown some promise because the routine
to read in the file data is isolated.

Do I have Orphan References in my Example?
As you can see from my example code (which is a direct paste from VBA), I
don't have an references that I would consider to be Orphan.... it's about as
simple as possible. I could even shrink it down to just Open & Close the
file and the Instance still remains.

On a side note, Pardon my lack of knowledge of orphan references...
If I have something that says ActiveSheet, can I just replace it with
something like this:

set MySheet = ActiveSheet
x = MySheet.Range("A1").value
set MySheet = Nothing

as oposed to just: x = ActiveSheet.Range("A1").Value

Thanks for the help,
MikeZz

 

[MikeZz]

Followup,
when I start typing xlApp and the ".", "Open" is not an available option in
the list.

It is difficult to critique imaginary code...
Do not use "New" in a variable declaration - see Chip Pearson's website if
you want the why of that.
ActiveSheet is an unqualified reference. Do not use it without specifying
the application it belong to. If fact I would not use it at all.
Suggested format for automation code...

Dim xlApp as Excel.Application
Dim WB as Excel.Workbook
Dim WS as Excel.Worksheet
Dim bigRange as Excel.Range
Set xlApp = New Excel.Application

Start of loop
Set WB = xlApp.Open(FileName)
Set WS = WB.Worksheets(1)

Do stuff using WS.Range("xxxx") or Set a reference to the range...
Set bigRange = WS.Range("xxxx")
(do not use "Selection" or "ActiveCell" or anything similar)
(do not use the "With" construct - use an object reference)

Set the worksheet and range references to Nothing
Close the Workbook and specify whether to save it or not.
Set the Workbook reference to Nothing
End of Loop

After looping thru all of the files, quit the application and set it to nothing.
'--
Jim Cone
Portland, Oregon USA



"MikeZz"
wrote in message
Hi Jim,
I am trying this method because I need to import data from several hundred
small excel files to process a summary of the data. The files are random in
nature with data scattered throughout. When I load the data using the
current instance, it doesn't seem to let go of the memory and by the 300th
file, it takes about 60x as long just to read the data into an array... and
the files are pretty small. There may be some unqualified references in the
original code but were talking about 50 different routines and functions... a
needle in a haystack. This method has shown some promise because the routine
to read in the file data is isolated.

Do I have Orphan References in my Example?
As you can see from my example code (which is a direct paste from VBA), I
don't have an references that I would consider to be Orphan.... it's about as
simple as possible. I could even shrink it down to just Open & Close the
file and the Instance still remains.

On a side note, Pardon my lack of knowledge of orphan references...
If I have something that says ActiveSheet, can I just replace it with
something like this:

set MySheet = ActiveSheet
x = MySheet.Range("A1").value
set MySheet = Nothing

as oposed to just: x = ActiveSheet.Range("A1").Value

Thanks for the help,
MikeZz

 

[MikeZz]

After Further Review, I think I figured out the problem...

Set WB = xlApp.Open(FileName)
should be
Set WB = xlApp.Workbooks.Open(FileName)

It is difficult to critique imaginary code...
Do not use "New" in a variable declaration - see Chip Pearson's website if
you want the why of that.
ActiveSheet is an unqualified reference. Do not use it without specifying
the application it belong to. If fact I would not use it at all.
Suggested format for automation code...

Dim xlApp as Excel.Application
Dim WB as Excel.Workbook
Dim WS as Excel.Worksheet
Dim bigRange as Excel.Range
Set xlApp = New Excel.Application

Start of loop
Set WB = xlApp.Open(FileName)
Set WS = WB.Worksheets(1)

Do stuff using WS.Range("xxxx") or Set a reference to the range...
Set bigRange = WS.Range("xxxx")
(do not use "Selection" or "ActiveCell" or anything similar)
(do not use the "With" construct - use an object reference)

Set the worksheet and range references to Nothing
Close the Workbook and specify whether to save it or not.
Set the Workbook reference to Nothing
End of Loop

After looping thru all of the files, quit the application and set it to nothing.
'--
Jim Cone
Portland, Oregon USA



"MikeZz"
wrote in message
Hi Jim,
I am trying this method because I need to import data from several hundred
small excel files to process a summary of the data. The files are random in
nature with data scattered throughout. When I load the data using the
current instance, it doesn't seem to let go of the memory and by the 300th
file, it takes about 60x as long just to read the data into an array... and
the files are pretty small. There may be some unqualified references in the
original code but were talking about 50 different routines and functions... a
needle in a haystack. This method has shown some promise because the routine
to read in the file data is isolated.

Do I have Orphan References in my Example?
As you can see from my example code (which is a direct paste from VBA), I
don't have an references that I would consider to be Orphan.... it's about as
simple as possible. I could even shrink it down to just Open & Close the
file and the Instance still remains.

On a side note, Pardon my lack of knowledge of orphan references...
If I have something that says ActiveSheet, can I just replace it with
something like this:

set MySheet = ActiveSheet
x = MySheet.Range("A1").value
set MySheet = Nothing

as oposed to just: x = ActiveSheet.Range("A1").Value

Thanks for the help,
MikeZz

 

[Jim Cone]

Looks correct.
Unfortunately, there is no syntax check when I write air code. <g>
Jim Cone


"MikeZz"
wrote in message
After Further Review, I think I figured out the problem...

Set WB = xlApp.Open(FileName)
should be
Set WB = xlApp.Workbooks.Open(FileName)

 

[MikeZz]

Hi Jim,
I did an even more basic test, just opening and closing an instance using
the following code.
It closed the instance as long as I didn't open and close a workbook - see
the 2 lines for opening and closing.

Sub CloseInstanceTest()

Dim xlApp2 As Excel.Application 'ADDED FOR MEMORY
Set xlApp2 = New Excel.Application 'ADDED FOR MEMORY
xlApp2.Visible = True
Dim wbCount, i

'If I don't do these lines of code, the Excel Instance closes.
'So as long as I don't open a file (which defeats the point), it works.
xlApp2.Workbooks.Open ("S:\C-0MNL0-0CB-001.xls")
wbCount = xlApp2.Workbooks.Count

For i = 1 To wbCount
xlApp2.Workbooks(i).Close
Next i
xlApp2.Quit
Set xlApp2 = Nothing

End Sub

 

[Jim Cone]

When I tried your code, It hung when trying to close the file unless I followed
the instructions I posted earlier:
"Close the Workbook and specify whether to save it or not."

The close line should read...
"xlApp2.Workbooks(i).Close SaveChanges:=False 'or True"
When I did the above, the new Excel instance closed without a problem.

Also, complications could arise, if your "S" drive is "elsewhere" or
the file is shared and in use.
--
Jim Cone
Portland, Oregon USA
(use object references when automating excel)




"MikeZz"
wrote in message
Hi Jim,
I did an even more basic test, just opening and closing an instance using
the following code.
It closed the instance as long as I didn't open and close a workbook - see
the 2 lines for opening and closing.

Sub CloseInstanceTest()

Dim xlApp2 As Excel.Application 'ADDED FOR MEMORY
Set xlApp2 = New Excel.Application 'ADDED FOR MEMORY
xlApp2.Visible = True
Dim wbCount, i

'If I don't do these lines of code, the Excel Instance closes.
'So as long as I don't open a file (which defeats the point), it works.
xlApp2.Workbooks.Open ("S:\C-0MNL0-0CB-001.xls")
wbCount = xlApp2.Workbooks.Count

For i = 1 To wbCount
xlApp2.Workbooks(i).Close
Next i
xlApp2.Quit
Set xlApp2 = Nothing
End Sub

 

[MikeZz]

Hi Jim,
I tried your adding this to the .close statement but it still didn't work.
SaveChanges:=False
I think it has something to do with our network.

However, I did resolve the issue by using this trick I found:

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam
As Long) As Long

'Get Handle I created.
HwndImport = xlApp2.hwnd

'Close Handle after Closing file, Quitting xlApp2 and setting = nothing.
SendMessage HwndImport, WM_CLOSE, 0, 0

Thanks for your help,