create a list of worksheet names (from a single folder, or open files)
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 |
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) "Drew" wrote in message oups.com... 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 |
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....htm#GoToSheet --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Drew" wrote in message oups.com... 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 |
All times are GMT +1. The time now is 04:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com