+ Reply to Thread
Results 1 to 5 of 5

Search closed Workbooks

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,065

    Search closed Workbooks

    I've got a workbook (I didn't create it) I've been using for a while, which searches through closed workbooks to find a search item. It works well, but I'd like to enhance it a bit and don't know how to achieve what I want. I've attached the workbook.

    After keying in the search term, the next action is to choose the file location. Clicking the button brings up the "Browse for Folder" box. That's where I want to make a change. I have to shift through various drives and folders to get to the folder I want to search, and often I have multiple searches to do. Since the present behaviour always start my search at the same folder view I'm constantly having to drive over the same route to get to the folder I'm searching. I'd like to have this box open up to the last folder accessed, instead of to the current default view.

    I've pasted the full code below, as well as attached the workbook. Much of it is over my head, but I think the change that needs to be made is in the "GetDirectory" function, specifically this line:

    Please Login or Register  to view this content.
    I don't understand that bit of code, but I think what it's doing is telling the "Browse for Folder" box to open to my desktop (it's actually my computer, with Desktop at the top).
    I thought that somehow I could capture the last accessed folder to a cell (say, M2), and have the code refer to that cell as the clue to where to start the next search, but don't know how to set that up.

    Another bit I'd like to add would be the ability to search for all the items in a list, instead of only for one item at a time. It could either go through all the workbooks looking for one item first, or look for all of the items in a single workbook and then move on to the next. Making this kind of change would involve some structure changes to show pertinent information, because rather than just listing the appropriate workbook it would have to show what items in the list correspond to that workbook.

    I appreciate you taking the time to read this post, and for any help you're able to provide.

    Thanks,
    John

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jomili; 02-02-2011 at 03:39 PM.

  2. #2
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Search closed Workbooks

    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,065

    Re: Search closed Workbooks

    Kenneth,

    I tried out your code, and it works well. It allows me to choose a folder, and pops up the messagebox telling me both the parent and the child folders I've chosen. I think I could adapt it to write those values to a cell, but I still don't know how to allow that to be used by the GetDirectory function.

  4. #4
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Search closed Workbooks

    Why would you need GetDirectory?

    Instead of:
    Please Login or Register  to view this content.
    Use:
    Please Login or Register  to view this content.
    Of course you would have to set the Directory string prior to using it and save it for multiple runs to memory or store to retrieve later in a cell, text file, etc.

  5. #5
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,065

    Re: Search closed Workbooks

    Kenneth,

    I changed your Test_GetFolder as shown below, which pastes the searched folder into cell M2.

    Please Login or Register  to view this content.
    In the "List Folders" macro, changing the "Directory" line as you indicated gives me an compile error saying the sub or function is undefined, which probably relates to your comment about "set the Directory string ". I'm not sure how to set it, so am kind of stuck here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1