+ Reply to Thread
Results 1 to 11 of 11

Include/Exclude Specific Instring Characters From A Querry Search

  1. #1
    Forum Contributor
    Join Date
    12-10-2008
    Location
    Austin
    Posts
    660

    Include/Exclude Specific Instring Characters From A Querry Search

    Hi group,

    I have a querry- file search application macro which searches all directories and subdirectories found within the provided string path and brings in specific data from spreadsheets in those directories. I would like to have the ability to exclude some of the subdirectories which are currently being querried.

    I'm wondering if it is possible to add additional code that will allow me to either include or exclude specific directories being querried.

    Here's an excerpt of the code:
    Please Login or Register  to view this content.
    Since the directory names are part of the string path, would it be possible to add something like the following to the code?

    Please Login or Register  to view this content.
    or

    Please Login or Register  to view this content.

    Any help is appreciated.

    Thanks,

    BDB
    Last edited by bdb1974; 01-05-2010 at 03:43 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Include/Exclude Specific Instring Characters From A Querry Search

    Hello bdb1974,

    It would be easier and faster to test specific directories using a RegExp. The RegExp can test for multiple matches without creating a loop. There are certain characters used in a file path that have special meaning in a RegExp: the backslash, and the period. To match these characters they must be preceded with a backslash, like this "\\", "\." The pipe character "|" is used as the "OR" operator in matching.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor
    Join Date
    12-10-2008
    Location
    Austin
    Posts
    660

    Re: Include/Exclude Specific Instring Characters From A Querry Search

    Thanks Leith.

    I added the code you provided but won't be able to test it till Tuesday.
    I do have a quick question,
    'Use True to include the directories or False top Exclude them
    If RegExp.Test(.FoundFiles(i)) = True Then
    OutPL.Cells(outrow, 2).Resize(1, 3).Value = .FoundFiles(i)
    End If

    Does this mean if I use True,it will only search the directories that I list in
    the RegExp.Pattern = "(\\TMP\\)|(\\WINDOWS\\)",therefore it will only retrieve data from TMP & WINDOWS?

    Hope that's not a retorical question :P

    Thanks and have a great weekend!

    BDB

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Include/Exclude Specific Instring Characters From A Querry Search

    Hello BDB,

    The RegExp.Test method checks the string for the specified pattern. If the pattern or in your case, part of the pattern, is found in the string, it returns true. You can add more paths to the pattern and separate them using the pipe character as shown in the code.

  5. #5
    Forum Contributor
    Join Date
    12-10-2008
    Location
    Austin
    Posts
    660

    Re: Include/Exclude Specific Instring Characters From A Querry Search

    Hi Leith,

    I finally got around to trying this and am getting an error for
    (Run-time error '424: Object required)

    Do I need to reference the script to any library, such as Microsft VBScript Regular Expressions 5.5 ?

    Thanks,

    BDB

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Include/Exclude Specific Instring Characters From A Querry Search

    Hello BDB,

    I don't know why you are getting that error. VBScript is part of Windows. This line of code ...
    Please Login or Register  to view this content.
    instantiates the object class for the Regular Expressions. This library has been available since Windows 2000. Perhaps you should post your workbook so I can review it.

  7. #7
    Forum Contributor
    Join Date
    12-10-2008
    Location
    Austin
    Posts
    660

    Re: Include/Exclude Specific Instring Characters From A Querry Search

    Leith,

    Here's the entire code which I condensed to fit character limit,

    Please Login or Register  to view this content.
    Thanks and Merry Christmas to you and everyone else.

    BDB

  8. #8
    Forum Contributor
    Join Date
    12-10-2008
    Location
    Austin
    Posts
    660

    Re: Include/Exclude Specific Instring Characters From A Querry Search

    .... bumping to top.

    Still trying to figure this one out...

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Include/Exclude Specific Instring Characters From A Querry Search

    Stopping at the first thing I saw,
    Please Login or Register  to view this content.
    ... the Set doesn't belong-- IgnoreCase is a Boolean property, not an Object.
    Entia non sunt multiplicanda sine necessitate

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Include/Exclude Specific Instring Characters From A Querry Search

    Hello BDB,

    Shg is right. That line will produce the error you describe, just not where the editor identifies it to be. Make the change and it should run fine.
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    12-10-2008
    Location
    Austin
    Posts
    660

    Re: Include/Exclude Specific Instring Characters From A Querry Search

    That fixed it!

    Wow, again, I have to say incredible.

    Leith,
    Thanks for the script

    and Thanks to SHG for the catch.

    BDB

+ 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