For Each loop leaves file open after .close

[Daniel]

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

 

[Ron de Bruin]

Hi Daniel

Remove the two Set sht lines and try again

 

[Daniel]

Hi Ron,

That didn't work.

Thanks,
Daniel

Hi Daniel

Remove the two Set sht lines and try again

 

[Ron de Bruin]

In my test there is no problem
Test it with a few new files and see if you have the same problem

 

[Daniel]

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("Sheet1").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