create a list of worksheet names (from a single folder, or open files)

[Drew]

I need to create a list of worksheet names (tabs) or all the files (I'd
like the file names as well, but think I've already found the add in
from http://www.tushar-mehta.com/excel/software/index.html that does
that part)

the purpose is to create a list - from which I will use concatenate to
make formulas linking to all the worksheets (they are all formated the
same) so I can summarize financial information. (I use concatenate)
make massive page of formulas and past special values to another page
(creating text that upon adding an = sign in front will become
formuals) I've found this works well on pages with 8,000 or so
formulas.

so in the end I'd like to have at least a file name and all sheet names
for that file - I can add the drive and folder and any formula specific
formatting like "[" etc

D:\FY05.Mar\[Excel.Filename.xls]sheet1'

D:\FY05.Mar\[Excel.Filename.xls]sheet2'
D:\FY05.Mar\[Excel.Filename.xls]sheet3'
D:\FY05.Mar\[Excel.Filename.xls]sheet4'
D:\FY05.Mar\[Excel.Filename2.xls]sheet1'
D:\FY05.Mar\[Excel.Filename2.xls]sheet2'

thanks, Drew

 

[Bob Phillips]

I don't know how Tushar's add-in outputs the results, but I have posted
similar code today that uses FSO to get the files and list them. The problem
with your requirement is that you either have to open the file (assuming it
is a workbook), or use ADOX to read the catalog information. This would
either use the output from Tushar's add-in (which as I say I am not familiar
with), or link directly into specific code to get the file data.

Any thoughts on that?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[David McRitchie]

If you want hyperlinks, you can build them with the
HYPERLINK Worksheet Function or create the object
type hyperlinks. More information on creating a list
of sheetnames with hyperlinks see
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

You do not actually need a hyperlink to get to a cell in another
worksheet or workbook, you can use an Event Macro
http://www.mvps.org/dmcritchie/excel/event.htm
such as a double click.
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#GoToSheet