Searching for text within other Excel files

[senormano]

I have a situation where I need to search for text strings within hundreds of Excel files; I believe that a macro of VBA script is ultimately required.

I have an index that includes a list of finished products, and paths to excel files that provide a list of component parts used in the manufacture of each finished product (parts lists for each product). I am looking for instances in which a particular part is used in a finished product (searches are for text strings).

I have a master spreadsheet that lists all of the parts list files and their corresponding finished good products, including a path to the parts lists. At this point I am happy with a flag (yes/no result) next to the index indicating whether the part was found.

I've attempted to accomplish my task using INDIRECT and INDIRECT.EXT, as the full file paths are themselves calculated from a formula. Long story short, the calculations take forever (reaching out over the network) and they are constantly recalculating.

I really only need to do each search once, as the indexed files will not change. I may need to occasionally re-run the search as new files are added or a new target part number is identified, but cannot envision needing to re-run the search on existing parts lists.

A mock worksheet is attached (backed out the "real" data). I hope to search for the Strings (e.g., String1, String2, String3 highlighted in yellow) within the files referenced in the indicated paths. I'd be happy with a yes/no or true/false result in the shaded green cells.

Insight is appreciated.

Note: I am also up for retaining a consultant to assist if that is in order (and so the actual data can be used under an NDA), but am unsure regarding the protocol for such a solicitation on these forums (or other, perhaps more appropriate forums), and extend my apologies if this note is overreaching.

[patel45]

For one string you can use this macro

Please Login or Register  to view this content.

[senormano]

Thank you patel45; the code, however, appears to break at this line:

Function ChooseFoldersSubfoldersFSO(ByVal sPath As String)

I am also curious, as the prompts aren't exactly clear; I hope to search each specific file (indicated in the path) for a string, rather than simply searching all of the excel files in a folder. I can't tell what the output of this may be.

[patel45]

I'm sorry, I pasted a wrong code, this is correct

Please Login or Register  to view this content.