Programming With Multiple Excel Workbook Objects
 

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

--
Hmm...they have the Internet on COMPUTERS now!

Programming With Multiple Excel Workbook Objects
 

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)


"MDW" wrote in message
...
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

--
Hmm...they have the Internet on COMPUTERS now!

Programming With Multiple Excel Workbook Objects
 

In option B, you would only close one workbook.

--
Regards,
Tom Ogilvy

"MDW" wrote in message
...
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

--
Hmm...they have the Internet on COMPUTERS now!

Programming With Multiple Excel Workbook Objects
 

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*

"Bob Phillips" wrote:

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)


"MDW" wrote in message
...
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

--
Hmm...they have the Internet on COMPUTERS now!