Hello. Great forum, I’ve learned quite a bit and have had some success in automating some browser and file directory processes. But I’m having trouble translating a concept to application for the the following requirements, I’ve played with Use Excel File, For Each Excel Row, For Each File in Folder and Find/Replace Value actions but can’t seem to figure out the correct combos. I’ve looked at several existing topic threads but I’m not sure that any scenarios are the same. Any help would be greatly appreciated.
• I have a list of keywords in an excel worksheet
• I have a folder containing many Excel workbooks in .xls and .xlsx formats. Each workbook can have multiple worksheets, with no standard worksheet naming conventions
• I’d like to understand if there’s a way to search each of these workbooks for each keyword/substring. For each workbook, the keywords could occur multiple times on multiple worksheets. Ideally, for any given matches on the keywords, I’d like to capture the following:
o The Keyword (substring) that matched (required)
o The string that the substring occurs in (required)
o The filename that includes the substring (required)
o The worksheet on which the substring occurred in (very nice to have)
o The cell in which the substring occurred (nice to have)
The input would need to be an excel file using a simple list. And the output would ideally be able to be recorded in the same excel file on another sheet (note the red text is just to demonstrate the keyword matches, sheet formatting is not needed). An example of spreadsheet input and output is below:
Many thanks in advance for any guidance!
Anil_G
supermanPunch
vishal.kp
mohamedalthaf
Neal369
Deep_HML
Manish540
keerthana.h
Floris_Maat
AAydi