Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007: Selecting multiple objects by drawing a selection box | Excel Discussion (Misc queries) | |||
How does one select multiple drawn objects in Excel 2007? | Excel Discussion (Misc queries) | |||
Referencing between two Excel Workbook Objects | Excel Worksheet Functions | |||
how to delete multiple autoshape or objects in excel | Excel Discussion (Misc queries) | |||
delete multiple autoshapes or objects in excel | Excel Discussion (Misc queries) |