Picking up data from workbooks which may not exist yet

[robzrob]

Hello

I've got a series of exisiting workbooks: Rota2008.07.21.xls,
Rota2008.07.28 and there are some which WILL exist: Rota2008.08.04,
Rota2008.08.11, etc. As is probably apparent, they're rotas named
according to the week-commencing date. I want to pull in data from
these workbooks on to a worksheet in another workbook which will show
2 of them, headed: 'Rota Current Week' and 'Rota Next Week'. So I
know I'm going to have to have a cell somewhere saying 'If today is >=
Rotayyyy.mm.dd but less than Rotayyyy.mm.dd+6, get the data from that
workbook and use it for 'Current Rota' and another similar one for
'Rota Next Week', but how do I refer and how do I get the data from
the individual cells to come across? The individual Rotayyyy.mm.dd
workbooks will always be closed.

 

[Pete_UK]

I think you will have to re-think how you are going to do this.
Normally, you would use the INDIRECT function to build up a cell or
range reference as a string and this can be accepted by other
functions, so that is how you would reference the other sheets/files.
However, INDIRECT can only work with open files.

Hope this helps.

Pete

 

[robzrob]

I think you will have to re-think how you are going to do this.
Normally, you would use the INDIRECT function to build up a cell or
range reference as a string and this can be accepted by other
functions, so that is how you would reference the other sheets/files.
However, INDIRECT can only work with open files.

Hope this helps.

Pete




- Show quoted text -

Hello Pete

Thought somebody might say that. Would the answer be at the end of a
road something like this: Set up the worksheet using the INDIRECT
function, then VBA code thus: Open the 2 files I'm interested in (this
week and next week) whenever the particular worksheet in my new
workbook is selected, update, close the 2 files. What do you think?

 

[Pete_UK]

That sounds as if it will work, though you will have to fix the values
before closing the 2 workbooks.

A similar approach would be to open each workbook in turn, then move
the sheet into the file containing the macro thereby automatically
closing the rota workbook (unchanged), and then the macro could
continue by establishing the formulae for you. The new sheets could be
renamed This_week and Last_week (or something similar), so the
formulae will always refer to the same (internal) sheet names.

You could also think about downloading the free add-in morefunc - this
has an INDIRECT.EXT function which is meant to allow you to get data
from closed files. I don't have it so I can't comment on it, but I've
seen lots of recommendations in other posts.

Hope this helps.

Pete