Find list of sheets of an unopened XLS file
 

I am trying to compile lots of data from hundreds of XLS files. The
sheet names containing the data are not the same; however, there are
some distingishing features that I can check. I have been using
formulas to refer to cells in a sheet so I don't have to open every XLS
file to get the data.

Is there any way I can get the names of the worksheets of the unopened
file so I can use the sheet names in a formula and then test that
value?

Thanks for the help.

Rock

Find list of sheets of an unopened XLS file
 

See this previous discussion. Particularly the function by KeepItCool:

http://tinyurl.com/8ybkh

--
Regards,
Tom Ogilvy

"Rock" wrote in message
oups.com...
I am trying to compile lots of data from hundreds of XLS files. The
sheet names containing the data are not the same; however, there are
some distingishing features that I can check. I have been using
formulas to refer to cells in a sheet so I don't have to open every XLS
file to get the data.

Is there any way I can get the names of the worksheets of the unopened
file so I can use the sheet names in a formula and then test that
value?

Thanks for the help.

Rock

Find list of sheets of an unopened XLS file
 

Tom,

Thanks for the link... however it seems that this code doesn't work for
my excel files (or I am not smart enough to get it to work). If I use
a new workbook it works just fine. I have added a chart to this
workbook and it works fine. I have copied all the sheets from my
workbook to a new workbook and it works fine. I copied all the sheets
and all the VBA modules and code and it works fine. If I try to use
the existing workbooks, then it doesn't work. The existing books
contain macros, images, and charts.

bTyp = aByt(9) 'types: 0WKS/1MACRO/2CHART

This value is 255 on the first loop, for some reason when opening the
existing XLS files. Does anyone know what this means or the cause?

Any suggestions on getting it to work with my existing sheets?

Thanks,

Rock