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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlApp.Quit - Doesn't Close Instance.... Any ideas?
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? "Jim Cone" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlApp.Quit - Doesn't Close Instance.... Any ideas?
Followup,
when I start typing xlApp and the ".", "Open" is not an available option in the list. "Jim Cone" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlApp.Quit - Doesn't Close Instance.... Any ideas?
After Further Review, I think I figured out the problem...
Set WB = xlApp.Open(FileName) should be Set WB = xlApp.Workbooks.Open(FileName) "Jim Cone" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlApp.Quit - Doesn't Close Instance.... Any ideas?
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) |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlApp.Quit - Doesn't Close Instance.... Any ideas?
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" wrote: 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) |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlApp.Quit - Doesn't Close Instance.... Any ideas?
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlApp.Quit - Doesn't Close Instance.... Any ideas?
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, "Jim Cone" wrote: 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 |
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 |