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

0
operaman (1)
4/15/2005 1:54:28 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
346 Views

Similar Articles

[PageSpeed] 13

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" <operaman@gmail.com> wrote in message
news:1113573268.548245.129690@z14g2000cwz.googlegroups.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
>


0
bob.phillips1 (6510)
4/15/2005 3:03:01 PM
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

---
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" <operaman@gmail.com> wrote in message news:1113573268.548245.129690@z14g2000cwz.googlegroups.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
>


0
dmcritchie (2586)
4/15/2005 3:58:38 PM
Reply:

Similar Artilces:

Problems with Public Folders replication
Hi guys, Some problems here. We have decided to install Exchange server 2003 SP1 on a new hardware with W2k3 SP1 on a new DC (yes, I know it is a bad practice, but...) instead of old Exchange server 2003 SP1 on an old DC on an old hardware. New one (additional) DC was brought live (with some minor problems, DNS, time, as always) without FSMO delegation (for now). Replication of users' accounts was smooth. Logs clear. After Exchange 2003 SP1 installation (with some DNS manipulations) also all was Ok. Dcdiag Ok. In ESM all Exchange servers are visible (old and new). So I have started ...

Want to add new folder to all existing mailboxes
Hello, I'm trying to find a way to add a "junk mail" folder to every mailbox in my site without having to log into each one manually. any ideas thank e "Eric" <ehansen@aruplab.nospam.com> wrote: >Hello, I'm trying to find a way to add a "junk mail" folder to every mailbox in my site without having to log into each one manually. any ideas? >thanks >e Well, if you've got an anti virus or anti spam product that's Exchange integrated then the package will generate them automatically. If you haven't, then deply Outlook 2003. If y...

multiple Domain name delivery problem
hi, I currently have Exchange Server 2003 Build 7638:2 SP2,. We have multiple domain names being delivered to the exchange store. I have nothad any problems, but i currently have one user that is not receiving emails with attachments from one certain "internet" sender to one of her email addresses, but the other address works fine & if they send emails without attachments, everything works fine from either address. I have had the user send them message with attachments to me & the user with the problem & i get the message, but not the other user! I even use message...

Create a distribution list from addresses in the 'To' list
Hi all, I belong to a social group, and our co-ordinator sends out an email from time to time with all the latest addresses in the To list. I already have a distribution list for the group, which then needs to be checked through and updated. Is there a simple way of transferring those addresses into my own distribution list? I'm using Outlook 2002. Everybody's fiddling around with bits of paper - I'll be a hero if I can make it easy for them! Thanks. Astley http://www.outlook-tips.net/archives/2004/20041105.htm -- Russ Valentine [MVP-Outlook] "Astley" <ast@...

Outlook 2003 and "Check Names"
Hi, We are testing Outlook 2003 at our office now and there is one very frustrating "feature" that is bugging all of us. When we create a new e-mail message and type in the first name of a person from the global address book and hit ctrl- k to check the name...we get a response that Outlook doesn't recognize the name and there are "no suggestions". The only way Check Name will successfully resolve the name is if we type it last name first (ie, Gates, Bill). This was not the case in Office XP or Outlook 2000. You could search by first name (or partial first name)...

worksheet tabs 01-06-10
Hi In the 2003 excel-- Is there a way to have the worksheet tabs automatically go in alphabetical order-- or do you have to do in manually? -- Thank-you! Ruth Ruth, Use this code: Sub ArrangeSheetsInOrder() Dim iCount As Integer Application.ScreenUpdating = False iCount = Sheets.Count For i = 1 To iCount - 1 For j = i + 1 To iCount If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move Before:=Sheets(i) End If Next j Next i End Sub "Ruth" wrote: > Hi > > In the 2003 excel-- Is there a way to have the worksheet tabs automatically ...

Input Excel 'Password to Open' through control in access form
Hi All, We know,Excel has prompt password to open it files. Is it possible to create a code that can supplies the excel prompt password?.So that when we open the excel file through our access control in a form, the excel files can be opened automatically.But when the excel files opened from its default icon,it will prompt a password first. ...

MS Money 95 data files
I hope that some one can answer this for me. I have used MS Money 95 for years, and it works just fine for me on Windows XP, however, I now have to reformat my hard drive, and have discovered that I can nolonger find my original install disk. Will the latest versions of Money still read the MS Money 95 data files. All that I have ever used the program for is to track my investments, and am unlikely to do any different in the future. Thanks Stan B In microsoft.public.money, Stan Banner wrote: >I hope that some one can answer this for me. >I have used MS Money 95 for years, and...

Invalid XML error when I open customization setting
I have a problem when I try to open customization setting after I import an entity. The system errored "Invalid XML" "The XML passed to the platform is not well-formed XML". Please recommend how to resolve this problem. Thanks. ...

Opening Excel Workbooks
I'm running into an issue where if I click on an Excel file through My Documents, it doesn't automatically bring it up. I get the toolbar but the actual spreadsheet doesn't appear on the screen. I have to click on the taskbar to get it to pop up. If I already have Excel active and I open a file through Excel, this doesn't happen. Any ideas? Here is a similar thread: http://www.excelforum.com/showthread.php?s=&threadid=237195 Rolli -- Message posted from http://www.ExcelForum.com Hi, Take a look at Tools-Options-General tab- uncheck ignore other application...

sharing of folders
I tried configuring my folder to share it with the others but unfortunately as i tour around the outlook i could find the permissions tab when i right click the properties of a certain folder. What should I do with this problem? Is there other way in order I could share my folder? Pls. help Thanks and regards You'll have to tell us more about your Outlook version and mail environment. In the meantime, you might want to take a look at http://www.slipstick.com/outlook/share.htm -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Po...

[Exchange 2003] Mail enabled public folder and sender address
I have a Windows 2003 domain with a native mode Exchange 2003 organization; there are some mail enabled public folders. When a user replies to a message in a public folder, the outgoing mail's sender address is the user's one; I want it to be the public folder's one, or at least I'd like the "reply-to" address to be set to the public folder's. How can I accomplish this? Thanks Massimo You would need to grant the user permissions to be able to Send As the public folder. By default, when you reply to a message that is in a PF, the PF is not composing the ...

cant start outlook pst file to large
Hi I hope somebody may be able to help im new to outlook and now I cant start outlook message says cannot open your default email folders pst file its too large as I cannot open outlook I cannot delete old folders See if this helps: http://support.microsoft.com/kb/296088 -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.html Author: The Lawyer's Guide to Microsoft Outlook 2007: http://tinyurl.com/ol4law-amazon "Ray" <Ray@discussions.microsoft.com> wrote in message news:B3B824DC-3C9B-4B1B-8EF3-61...

Where is the workspace file stored?
I was given a project folder containing several dozen files. So where is the workspace file normally located? If I don't have a workspace file how can I cerate one? Using VC version 6. TIA I haven't used VC6 for some time, but if I remember right you can just open the .dsp file and it will create a .dsw file for you. It typically goes in the same folder with the code. Tom "Phisherman" <noone@nobody.com> wrote in message news:be93539ccf8a6f25ibh4vt6ggmtbi024mq@4ax.com... >I was given a project folder containing several dozen files. So where > is the wor...

How do I create several NEW templates from start to finish please?
Hi knowledgeable people. I am looking for instructions on how to create a range of basic templates that I can use, perhaps even by selecting them as 'stationery', for example; if I go to >New >Format >Apply stationery, or any other similar method. I can create new html stationery backgrounds, but this is NOT what I am looking for, as a lot of people still read email in plain text only...does this make sense? Plain text templates would be fine. I simply can't find any info on creating email templates anywhere to suit this Windows Live Mail Desktop versio...

Change File Locations to Private Drive (not Folder)
Hi, I know that I can use "File Locations" in "Options" so that whenever I use the "Open..." command in Word, it will open to a specific folder. I'm wondering if there is a way that this can be applied not to a folder but to a specific drive on a network. Our company has a main public drive and has also assigned each of the Staff their own private drive. Is there a way to access the "main page" (for lack of a better term) of my private drive each time I use "Open..." in Word? Right now it goes to "My Computer" or ...

adding name /creating field/query?
Hello, I can create an invoice_number field in a query using the primary field ID from the main table as invoice_number: ID but if ID say is 100, I cannot work out how to create renewal_invoice_100 Cheers Geoff Geoff We aren't there. We can't see what you're looking at. Where did "renewal_invoice_100" come from and what does it mean? Please post the SQL statement of the query you are trying to use. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://micro...

owa not resolving names properly
Exchange 5.5 sp4 - just recently OWA is not resolving addresses properly - the administrator at this site states: > There is a strange situation happening when using OWA to send emails. If > we try to send an email to an account out of our domain (i.e. > jravazzano@yahoo.com) the recipient name and domain get wrongly resolved > to our own domain thus generating a message undeliverable. > > For example using OWA a test email was sent to "jravazzano@yahoo.com" the > address gets resolved to j@ourdomain.com The admin is going to give me an account to test with, but...

Creating new attributes
When using Schema Manager to create new attributes, I'm not able to create a new attribute with "Lookup" as type, "Lookup" is not availabe as one of the type options. But yet some of the exist fields, for example, in Account table came with "Lookup" as type. Why? Is there a work-around? Please help. Thanks. You cannot add custom lookup's to the current release of MSCRM. The only way around is to build a custom .aspx page and build your own. Might be possible in the next release. "HEC" wrote: > When using Schema Manager to crea...

how do i add "Software Digrams" Folder in my Visio 2003
how do i add software diags folder in visio 2003 version. Like if i go to File->New it shows folders like brainstorming, project schedule, block diagrams but in my case its missing the Software Diags folder which contains activity diags, web mock ups, use case diags etc. please let me know how to get that template. thanks. Ankur What edition of Visio are you using? Check by going to the About Microsoft Office Visio dialog from the Help menu. "Ankur" <Ankur@discussions.microsoft.com> wrote in message news:F0D10BF0-A059-4A9C-B615-B99F3916F4E5@microsoft.com... > how...

Unexpected error message on closing an Excel file
Suddenly I am getting the following message when I try to close a workbook: "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, and try again" The mysterious thing is that it does not happen consistently and that, after I click OK after the above message, I can still save the file. What might be the cause of this error message and can the "invalid reference" be tracked down using one of the utility add-ins such as J. Walkenbach's PUP? If it only happens when you close ...

Drag and Drop file/directory names on Tree Control.
hi, I am writing an application in which i have one Tree control. Now i want that , when user drag & drop any file/folder on tree control the file name/folder name should be add in the tree control item. User may drag and drop file from desktop or may be from directory. I just want the path of that file/foder name nothing else. so plz tell me will it be possible ... Plz Help me............. Klic..... > I am writing an application in which i have one Tree control. >Now i want that , when user drag & drop any file/folder on tree >control the file nam...

How to get rid of personal folders view in nagivation pane with MA
Hi, trying to help a friend who migrated to hosted mail with MAPI. She is running Oultlook 2003 with latest updates. In the navigation there are personal folders and MAPI folders. The personal folders always show at top and expanded, with the MAPI folders below and not expanded. She only wants to see the MAPI folders expanded in the navigation view. I tried deleting the personal folders and it would not let me and it will not let you deliver to MAPI, which I understand why. Does anyone know how to make this happen? Thanks. Is this a hosted Exchange account? You can change the...

Outlook 2003
Hello, we have one client pc with Outlook 2003 where the following problem occurs: if a mail is replied from a user created sub folder of the inbox, the reply will not appear in sent mails, but will end up in the same sub folder, sender and recipient appear as identical. The mail goes out properly though, it's just that Outlook sets internally sender = recipient and bounces the reply back to the subfolder in which the original mail is stored. This happens on a Win 2000 machine with SP4 and all updates, the other clients use Outlook 2000 or XP and there is no such problem. There are no ru...

Install Exchange 2003 Server on new Hardware with same name
I got a new server to move my Exchange Server Installation. I want to keep the name I'm using now for my Server. Already read TechNet Article "How to Move Exchange Server 2003 to New Hardware and Keep the Same Server Name". Is there anything else that I should be aware of that is not on this TechNet article? I'm using a different Disk Configuration, does this matters? Should this be a smooth process? Thanks for any help you can give. The drive letters MUST match, because the paths of the databases are at the Active Directory. The disks can have different sizes than the or...