+ Reply to Thread
Results 1 to 11 of 11

Case insensitive search for string within folder of excel files

  1. #1
    Registered User
    Join Date
    02-18-2011
    Location
    cork
    MS-Off Ver
    Excel 2003
    Posts
    15

    Smile Case insensitive search for string within folder of excel files

    I need to be able to do a case insensitive search for a string in a specific folder containing multiple excel files none of which have the same number of worksheets.

    The result of the search should be made available in a new file which will specify where the matches were found.

    I look forward to your kind help in this regard.
    Last edited by vba_novice; 03-22-2011 at 11:26 PM. Reason: confirm SOLVED (Thank you so much JBeaucaire)

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Case insensitive search for string within folder of excel files

    Searching filenames in a specific folder is inherently a case-non-sensitive event. MYFILE.xls and MyFile.xls would register as the same file.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    02-18-2011
    Location
    cork
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Case insensitive search for string within folder of excel files

    Dear JBeaucaire, What I actually need to do is examine a column of values in SHEET1, of a specific excel file and then identify the occurrences of all of these values inside the worksheets of a bunch of other excel files (all having multiple sheets) located in a single folder. Your assistance will be highly appreciated. Thank you.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Case insensitive search for string within folder of excel files

    Without referring to macros or "method", go and layout step by step how you would do this manually, be very specific.

    1) go to folder C:\MyFiles\
    2) Open file#1 (we will eventually check every file in this folder)
    3) Search "Sheet1" column A for "cat" (partial string match or entire cell match?)
    4) If found, write down the filename and cell reference in report
    5) Continue searching that file for more instances of "cat", note each one
    6) Close file

    7) Repeat steps 2-7 for every file in folder.


    Something like that, except make it 100% accurate for your steps.
    Last edited by JBeaucaire; 03-22-2011 at 12:30 PM.

  5. #5
    Registered User
    Join Date
    02-18-2011
    Location
    cork
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Case insensitive search for string within folder of excel files

    Dear JBeaucaire, Thank you. Let me try this.

  6. #6
    Registered User
    Join Date
    02-18-2011
    Location
    cork
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Case insensitive search for string within folder of excel files

    Dear JBeaucaire,

    I have pasted here the code. I get the "Object required" error.

    1. I have 3nos files located in E:\0_test search. Each file contains multiple sheets.
    2. SearchMultipleFiles.xls contains Sheet1 which contains a string in each of the cells in range A1:A4
    3. I wish to sequentially search for each one of these strings (mentioned in item-2 above) in each of the 3nos file located in E:\0_test search.
    4. I wish to dump the found strings into SearchMultipleFiles.SearchResults

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    02-18-2011
    Location
    cork
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Case insensitive search for string within folder of excel files

    Dear JBeaucaire, I apologise but I cannot see the button for file attaching.

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Case insensitive search for string within folder of excel files

    Go advanced, paperclip icon.

  9. #9
    Registered User
    Join Date
    02-18-2011
    Location
    cork
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Case insensitive search for string within folder of excel files

    Dear JBeaucaire,

    I should have inserted into the following line of code "iCellXaminRow" instead of "2" however, problems other than this exist. Thank you for your kind attention:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Case insensitive search for string within folder of excel files

    Here's my take on that:
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    02-18-2011
    Location
    cork
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Case insensitive search for string within folder of excel files

    Dear JBeaucaire,

    You great help is very highly appreciated!
    Your code is working very well.
    Thank you for your generousity in time and expertise.
    My very best wishes for you and yours.

+ 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