Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlApp.Quit - Doesn't Close Instance.... Any ideas?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlApp.Quit - Doesn't Close Instance.... Any ideas?
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 -- HTH... Jim Thomlinson "MikeZz" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlApp.Quit - Doesn't Close Instance.... Any ideas?
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 Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "MikeZz" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlApp.Quit - Doesn't Close Instance.... Any ideas?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlApp.Quit - Doesn't Close Instance.... Any ideas?
I'm using Excel 2003 with SP3 installed.
"Jim Cone" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlApp.Quit - Doesn't Close Instance.... Any ideas?
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" wrote: Which office application are you using to run your code? -- Jim Cone Portland, Oregon USA |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlApp.Quit - Doesn't Close Instance.... Any ideas?
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" wrote: 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" wrote: Which office application are you using to run your code? -- Jim Cone Portland, Oregon USA |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlApp.Quit - Doesn't Close Instance.... Any 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 "Jim Cone" wrote: 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" wrote: Which office application are you using to run your code? -- Jim Cone Portland, Oregon USA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel instance won't quit | Excel Discussion (Misc queries) | |||
Instance still there in task manager after xlapp.Application.Quit | Excel Programming | |||
unable to close macro using auto_close or application.quit | Excel Programming | |||
Close Open Woorkbook and Quit Excel | Excel Programming | |||
Close second instance of excel | Excel Programming |