1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

excel closed workbooks

Discussion in 'Business Applications' started by drafter, Jul 5, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. drafter

    drafter Thread Starter

    Joined:
    Nov 21, 2000
    Messages:
    419
    I would like to search for some text in about 15 different closed workbooks. Is this possible?

    thanx
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Yes, you can do it while they are closed or open them, search and then close them.
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    This can easily be done using a VBA macro. Are all the workbooks located in the same directory? Give more details on what you want to do and we can provide more assistance.

    Regards,
    Rollin
     
  4. drafter

    drafter Thread Starter

    Joined:
    Nov 21, 2000
    Messages:
    419
    I have about 15 different excel files in the same directrory. I would like to search for a certian string of text.
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    drafter, you can use the VBA Function FileSearch to do that, it does not need any special VBA.
    If you open the VBA Editor and use it's Help (which is different from the standard Excel Help)
    and search for FileSearch it will show you the Code.
    If you have a problem I can post it.
     
  6. drafter

    drafter Thread Starter

    Joined:
    Nov 21, 2000
    Messages:
    419
    The following is the VBA I found. Now what do I do with it?

    With Application.FileSearch
    If .Execute() > 0 Then
    MsgBox "There were " & .FoundFiles.Count & _
    " file(s) found."
    For i = 1 To .FoundFiles.Count
    MsgBox .FoundFiles(i)
    Next i
    Else
    MsgBox "There were no files found."
    End If
    End With
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Have a look at the attached workbook. the data that you are searching for goes in Cell H1 and you can browse to the Folder containing the Excel files, it can also be set to search sub folders as well.
     

    Attached Files:

  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    OBP, that's really handy! Thanks!!
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    slurpee, I have an Access version in my Document Management database that you might also like. The Access version does a lot more.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/592059

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice