Hello World,

Is it possible, either through VBA or a formula, to check the value of
cell in a closed workbook if you don't know the sheet name?

All I know is that it will always be the first and probably only sheet
in the Workbook.

Kind regards,
Nick.

why not open the workbook and retrieve the value, then close the workbook

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Hello World,

Is it possible, either through VBA or a formula, to check the value of
cell in a closed workbook if you don't know the sheet name?

All I know is that it will always be the first and probably only sheet
in the Workbook.

Kind regards,
Nick.

Tom Ogilvy wrote:
why not open the workbook and retrieve the value, then close the workbook


Hi Tom,

Thanks for taking the time to respond.

The reason is that the user will have a large number of workbooks to
process and although the ones we are interested are fairly small they
may be sitting in the same directory as many other heavy-weight
workbooks (15-20 Mb) which will take a while to load.

My boss is reluctant to try and force the users to keep these workbooks
in separate directories or adhere to specific naming conventions (my
first suggestions) but wants them to be able to simply point to the
directory that contains the files.

My desire to not open all the files is simply to speed up the checking
of which files should be processed.

Kind regards,
Nick

You can use ADO if you want
http://www.rondebruin.nl/ado.htm

But open it is also a good option

--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message oups.com...
Hello World,

Is it possible, either through VBA or a formula, to check the value of
cell in a closed workbook if you don't know the sheet name?

All I know is that it will always be the first and probably only sheet
in the Workbook.

Kind regards,
Nick.

Many thanks Ron, I'll give this a go and let you know how I get on (may
not be for a couple of days as I'm fire-fighting another project at the
mo').

Kind regards,
Nick.

Update...

Hi Ron,

I found a few minutes to follow your link and try out the code therein.
Unfortunately it seems ADO still requires you to specify a string for
the sheet name. I tried changing the datatype of SourceSheet to integer
and then passing it a 1 to specify the first sheet but this failed to
work.

I strongly suspect this simply isn't possible but I will be grateful
for any other suggestions.

Regards,
Nick.

from your response to me, it sounds like you don't even know which workbooks
to process besides not knowing the sheet name.

Do you determine if a book should be processed by retrieving some
information from it?

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Update...

Hi Ron,

I found a few minutes to follow your link and try out the code therein.
Unfortunately it seems ADO still requires you to specify a string for
the sheet name. I tried changing the datatype of SourceSheet to integer
and then passing it a 1 to specify the first sheet but this failed to
work.

I strongly suspect this simply isn't possible but I will be grateful
for any other suggestions.

Regards,
Nick.

Yes Tom, That's the gist of it. :)

Regards,
Nick.