Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each loop leaves file open after .close
Hi,
I'm running Excel 2003. I've been trying to open & close a bunch of files, but am finding that it leaves the file open even after I use the .close method and set the variables = nothing. The files seems to be released upon subsequent execution of the this line: "For Each sht In wkbk.worksheets" Am I not referencing something properly (or not releasing it properly)? I tried setting the sht reference to nothing both before & after the workbook was closed, and I tried inserting "Set sht = wkbk.sheets("Title")" right before the "for each ..." statement, but that didn't help either. Here's the code: Dim chngWkbk As Workbook Dim sht As Worksheet Dim wkbk As Workbook Dim curLn As Integer Set chngWkbk = ThisWorkbook 'work from the master file For curLn = chngWkbk.Sheets("Sheet Setup").Range("E1").Value To chngWkbk.Sheets("Sheet Setup").Range("F1").Value 'get the filename from the master file If chngWkbk.Sheets("Sheet Setup").Range("A" & curLn).Value < "" Then Set wkbk = Application.Workbooks.Open(chngWkbk.Sheets("Sheet Setup").Range("A" _ & curLn).Value, , 0, , , , , , , -1) ' *** files stay open until this line is executed the next time through the loop ****' For Each sht In wkbk.Worksheets 'do stuff Next sht Set sht = Nothing wkbk.Close Set sht = Nothing Set wkbk = Nothing End If Next curLn Thank you, Daniel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each loop leaves file open after .close
Hi Daniel
Remove the two Set sht lines and try again -- Regards Ron de Bruin http://www.rondebruin.nl "Daniel" wrote in message ups.com... Hi, I'm running Excel 2003. I've been trying to open & close a bunch of files, but am finding that it leaves the file open even after I use the .close method and set the variables = nothing. The files seems to be released upon subsequent execution of the this line: "For Each sht In wkbk.worksheets" Am I not referencing something properly (or not releasing it properly)? I tried setting the sht reference to nothing both before & after the workbook was closed, and I tried inserting "Set sht = wkbk.sheets("Title")" right before the "for each ..." statement, but that didn't help either. Here's the code: Dim chngWkbk As Workbook Dim sht As Worksheet Dim wkbk As Workbook Dim curLn As Integer Set chngWkbk = ThisWorkbook 'work from the master file For curLn = chngWkbk.Sheets("Sheet Setup").Range("E1").Value To chngWkbk.Sheets("Sheet Setup").Range("F1").Value 'get the filename from the master file If chngWkbk.Sheets("Sheet Setup").Range("A" & curLn).Value < "" Then Set wkbk = Application.Workbooks.Open(chngWkbk.Sheets("Sheet Setup").Range("A" _ & curLn).Value, , 0, , , , , , , -1) ' *** files stay open until this line is executed the next time through the loop ****' For Each sht In wkbk.Worksheets 'do stuff Next sht Set sht = Nothing wkbk.Close Set sht = Nothing Set wkbk = Nothing End If Next curLn Thank you, Daniel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each loop leaves file open after .close
Hi Ron,
That didn't work. Thanks, Daniel Ron de Bruin wrote: Hi Daniel Remove the two Set sht lines and try again -- Regards Ron de Bruin http://www.rondebruin.nl "Daniel" wrote in message ups.com... Hi, I'm running Excel 2003. I've been trying to open & close a bunch of files, but am finding that it leaves the file open even after I use the .close method and set the variables = nothing. The files seems to be released upon subsequent execution of the this line: "For Each sht In wkbk.worksheets" Am I not referencing something properly (or not releasing it properly)? I tried setting the sht reference to nothing both before & after the workbook was closed, and I tried inserting "Set sht = wkbk.sheets("Title")" right before the "for each ..." statement, but that didn't help either. Here's the code: Dim chngWkbk As Workbook Dim sht As Worksheet Dim wkbk As Workbook Dim curLn As Integer Set chngWkbk = ThisWorkbook 'work from the master file For curLn = chngWkbk.Sheets("Sheet Setup").Range("E1").Value To chngWkbk.Sheets("Sheet Setup").Range("F1").Value 'get the filename from the master file If chngWkbk.Sheets("Sheet Setup").Range("A" & curLn).Value < "" Then Set wkbk = Application.Workbooks.Open(chngWkbk.Sheets("Sheet Setup").Range("A" _ & curLn).Value, , 0, , , , , , , -1) ' *** files stay open until this line is executed the next time through the loop ****' For Each sht In wkbk.Worksheets 'do stuff Next sht Set sht = Nothing wkbk.Close Set sht = Nothing Set wkbk = Nothing End If Next curLn Thank you, Daniel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each loop leaves file open after .close
In my test there is no problem
Test it with a few new files and see if you have the same problem -- Regards Ron de Bruin http://www.rondebruin.nl "Daniel" wrote in message ups.com... Hi Ron, That didn't work. Thanks, Daniel Ron de Bruin wrote: Hi Daniel Remove the two Set sht lines and try again -- Regards Ron de Bruin http://www.rondebruin.nl "Daniel" wrote in message ups.com... Hi, I'm running Excel 2003. I've been trying to open & close a bunch of files, but am finding that it leaves the file open even after I use the .close method and set the variables = nothing. The files seems to be released upon subsequent execution of the this line: "For Each sht In wkbk.worksheets" Am I not referencing something properly (or not releasing it properly)? I tried setting the sht reference to nothing both before & after the workbook was closed, and I tried inserting "Set sht = wkbk.sheets("Title")" right before the "for each ..." statement, but that didn't help either. Here's the code: Dim chngWkbk As Workbook Dim sht As Worksheet Dim wkbk As Workbook Dim curLn As Integer Set chngWkbk = ThisWorkbook 'work from the master file For curLn = chngWkbk.Sheets("Sheet Setup").Range("E1").Value To chngWkbk.Sheets("Sheet Setup").Range("F1").Value 'get the filename from the master file If chngWkbk.Sheets("Sheet Setup").Range("A" & curLn).Value < "" Then Set wkbk = Application.Workbooks.Open(chngWkbk.Sheets("Sheet Setup").Range("A" _ & curLn).Value, , 0, , , , , , , -1) ' *** files stay open until this line is executed the next time through the loop ****' For Each sht In wkbk.Worksheets 'do stuff Next sht Set sht = Nothing wkbk.Close Set sht = Nothing Set wkbk = Nothing End If Next curLn Thank you, Daniel |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each loop leaves file open after .close
Thanks for looking into this Ron.
I tried the test with a new workbook, and the same symptoms are still there, but maybe they don't actually matter? When I step through the program, when each file opens it appears in the VBAProject window (by default at the top-left of the VBA window). It doesn't disappear until the next time the 'For Each ...' statement is encountered. However, when the sub ends, the last file disappears from the VBAProject window. I've been burnt on the issue of not removing references properly before, so I'd like to learn the proper techniques. Here's the test code I used: Sub testOpen() Dim i As Integer Dim wkbk As Workbook Dim tWkbk As Workbook Dim test As String Set tWkbk = ThisWorkbook For i = 1 To 3 Set wkbk = Application.Workbooks.Open(tWkbk.Worksheets("Sheet 1").Range("A" & i).Value, , -1) For Each sht In wkbk.Worksheets 'do nothing test = sht.Range("A1").Value Next sht wkbk.Close 0 Set wkbk = Nothing Next i End Sub Thank you, Daniel Ron de Bruin wrote: In my test there is no problem Test it with a few new files and see if you have the same problem -- Regards Ron de Bruin http://www.rondebruin.nl "Daniel" wrote in message ups.com... Hi Ron, That didn't work. Thanks, Daniel Ron de Bruin wrote: Hi Daniel Remove the two Set sht lines and try again -- Regards Ron de Bruin http://www.rondebruin.nl "Daniel" wrote in message ups.com... Hi, I'm running Excel 2003. I've been trying to open & close a bunch of files, but am finding that it leaves the file open even after I use the .close method and set the variables = nothing. The files seems to be released upon subsequent execution of the this line: "For Each sht In wkbk.worksheets" Am I not referencing something properly (or not releasing it properly)? I tried setting the sht reference to nothing both before & after the workbook was closed, and I tried inserting "Set sht = wkbk.sheets("Title")" right before the "for each ..." statement, but that didn't help either. Here's the code: Dim chngWkbk As Workbook Dim sht As Worksheet Dim wkbk As Workbook Dim curLn As Integer Set chngWkbk = ThisWorkbook 'work from the master file For curLn = chngWkbk.Sheets("Sheet Setup").Range("E1").Value To chngWkbk.Sheets("Sheet Setup").Range("F1").Value 'get the filename from the master file If chngWkbk.Sheets("Sheet Setup").Range("A" & curLn).Value < "" Then Set wkbk = Application.Workbooks.Open(chngWkbk.Sheets("Sheet Setup").Range("A" _ & curLn).Value, , 0, , , , , , , -1) ' *** files stay open until this line is executed the next time through the loop ****' For Each sht In wkbk.Worksheets 'do stuff Next sht Set sht = Nothing wkbk.Close Set sht = Nothing Set wkbk = Nothing End If Next curLn Thank you, Daniel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Close & save a file at set time only IF the file is open | Excel Programming | |||
Loop through ".DAT" files, open run code, close next | Excel Programming | |||
VBA - on a button event, open another closed file, post changes, close file | Excel Programming | |||
Automate open file, update links, run macro, close and save file | Excel Programming | |||
Can I have a loop to open a set of workbooks get some data, close it one a time. | Excel Programming |