Closing some workbooks without others! 1

[Nitibob]

Hi all,

I'm sure there will be a really simple solution.

I have to open lots of separate workbooks....do some manipulation on these books and then close them BUT I have a few workbooks that need to always remain open.

Has anyone got any ideas?

 

[RobBroekhuis]

Why not open all those workbooks you need to manipulate one at a time, and close before opening the next one? That would make keeping track a lot easier. Otherwise, you can either explicitly keep track of the workbooks by keeping an array of their names, or explicitly keep track of the ones you don't want to close.
Rob
Rob

 

[Nitibob]

Hi Rob

Thanks for the feedback. I like the idea of keeping track of the ones I want to keep open. I'm a little unsure of how to use arrays...and how I can use arrays in this situation...hope you can help

 

[RobBroekhuis]

Say you have three files you'd like to keep open. You could, as you suggest, store their names in an array:

dim WBkeep(3) as string
dim wb as workbook
dim i as integer
dim CloseIt as boolean

WBkeep(1)="Keep this one.xls"
WBkeep(2)="And this one.xls"
WBkeep(3)="And another one.xls"

now, when you go to close the workbooks:

for each wb in workbooks
CloseIt=true
for i=1 to 3
CloseIt=CloseIt and (wb.name<>wbkeep(i))
next i
if CloseIt then wb.close
next wb

That's just one way to do it. You could also use a select case statement, such as:

for each wb in workbooks
select case wb.name
case &quot;Keep this one.xls&quot;:
case &quot;And this one.xls&quot;:
case &quot;And another one.xls&quot;:
case else
wb.close
end selct
next wb

(you can only use this approach if you know the ones to keep ahead of time)


next wb
Rob

 

[Nitibob]

Hi Rob,

Thanks for the excellent feedback...its given me the info I need to carry on

Cheers

Mike