Programming With Multiple Excel Workbook Objects

[Guest]

Hey all.

This may be an issue of semantics more than anything, I'm not sure.

Say I'm using the FileSystemObject to loop through a folder full of Excel
files using "For Each ... Next". On every iteration, I'm setting a Workbook
object using the code "Set objWB = objXL.Workbooks.Open(strFolderPath &
objFile.Name)".

Is it better for me to close each wokbook within the loop (there could be
upwards of 500 workbooks), or close the workbook once, after the loop? In
either case, I will set the variable to nothing after the loop.

In other words, which is better (or is there a difference) -

OPTION A:

For Each objFile In objFolder.Files
Set objWB = objXL.Workbooks.Open(strFolderPath & objFile.Name)
' Do stuff
objWB.Saved = True
objWB.Close
Next
Set objWB = Nohing

OPTION B:

For Each objFile In objFolder.Files
Set objWB = objXL.Workbooks.Open(strFolderPath & objFile.Name)
' Do stuff
Next
objWB.Saved = True
objWB.Close
Set objWB = Nohing

 

[Bob Phillips]

I think you may be mixing things up a wee bit in your terminology.

Option 2 only saves closes the last opened workbook, the other 499 are still
open.

Go for option 1, close each down when done with. The Set ... = Nothing is
just releasing the workbook variable from memory, it has nothing to do with
closing.



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

In option B, you would only close one workbook.

 

[Guest]

Alright, thanks.

Yeah, I wasn't QUITE sure if setting the variable to Nothing would close
those workbooks or not. For some reason I was under the impression that it
did. No idea. *shrugs*

 

[Tom Ogilvy]

That is true for an application, but the workbook isn't an application.