opening and clsing workbooks dynamically. names and paths created via INDIRECT formula

Good Afternoon All,

Please Login or Register  to view this content.

I use the above code to open another workbook. I do this as some of the formula's I use are dragging data via indirect formulae, and it seems better to have the workbooks in question open rather than using any of the workarounds due to the speed issue's they have.

Usually the values of SuFILE1 to sUFILE5 would be identical but there are occasions (when the week crosses over from one month to the next) when there maybe two values (and hence two different workbooks) opened. What I'd like to now accomodate is if/when the user changes a week number selector these values are evaluated and if they have changed (selecting week 12 and then 34 for example) any workbook no longer required is closed, any already open workbook is left open and any new workbook required to be open is then open.

the workbook names are created via formula in the ranges H30 through to H34.

I've added the file i'm working on in hpoes that it makes things a little easier for you.

You could use a loop to go through the open workbooks and check if they are open, something like:

Please Login or Register  to view this content.

closing the other workbooks is trickier but if they had some sort of constant filename you could include that in the for loop, otherwise you could end up closing other workbooks that are open and aren't part of the SuFiles...

Thanks for the help Yudlugar,

unfortunately the names might not be constant, although there might be similiarities, would it be better/posisble to close each file listed as sUFILE1 - sUFILE5 updated the values and then open the new files (even if it's the same files) or is there a way we cna store the "current values", refresh the values and compare the stored versions to the new ones, and open and close as appropriate?

I don't know how to go about getting the old versions of sUFILE1 - sUFILE5, unless you had a range on your workbook where you kept which files you had opened so you knew which ones to close.

You could maybe do something like:
Range("A1") = Range("A1") & sUFILE1
when you open a file, then when you loop through the workbooks, do it like this:

Please Login or Register  to view this content.

would it help If I moved the element of the sub that opens the new workbook to a seperate sub, so that we could compare the currently open workbooks against the values of sUFile, and if different open/close?

if so how would the latter part be coded?

This would close everything that isn't an sUFILE or the workbook you run the sub from but I don't like doing it this way - if you are working on another workbook that is part of something else you might accidently close it and lose the work...

Please Login or Register  to view this content.

hmm good point I hadn't thought of that - is there a way to capture every other workbook open at the time this workbook opens, and add it to the "safe list" so that only newly opened files that are now not in the new values for sUfile are closed?

The fifth post I wrote earlier was the reverse - adding the workbooks it opens to a "not safe" list so it knows which ones to close. This would be better as if the user opened another workbook after this one then it wouldn't be added to the safe list.

Originally Posted by yudlugar

I don't know how to go about getting the old versions of sUFILE1 - sUFILE5, unless you had a range on your workbook where you kept which files you had opened so you knew which ones to close.

You could maybe do something like:
Range("A1") = Range("A1") & sUFILE1
when you open a file, then when you loop through the workbooks, do it like this:

Please Login or Register  to view this content.

i'm getting an error on this line: elseif not instr (1, Range("A1"), wb.name= 0 then

It's missing a bracket at the end, should be wb.name) = 0

Thanks for that using:

Please Login or Register  to view this content.

I get an "Next without For" error

ok fixed that error with this:

Please Login or Register  to view this content.

but now it closes everything thats open inclduing this report.