keyword search numerous files in one folder

[hk4kim]

Good day everyone, I was hoping if someone can kindly come up with a VBA code to help ease my stress.
I have a specific folder in the directory "\\Fps1ids\hmkqc\Inspection_Test Sheet\QAF" and it contains countless number of excel spreadsheet files, where each of them have a unique identity code in its name. (6 digit number)

Now, I have a masterfile spreadsheet that contains all of these identity numbers and I want to search the file name by this number to return two specific fields in the selected file.

For example, the first file has all the codes to be used as keyword search prompt, and once the file with matching keyword has been found, return the cell value to column E and J.

The second attached is one of many similar looking files that will be searched through to find a match.

As you can see, the file name contains the 6 digit keyword code.
(i.e QAF 9584 801000-HK1574-84inch-0545 )

Basically, find the file with matching 6 digit number and return the cell value for DIE # and CURE TIME. (HK 1574 and 3.50 respectively and no unit measures) When performing this, you need to do this by string searching CURE TIME and DIE # and return the value by moving a few cells to the right.(This format will stay the same as well as the number of cells to the right you move). Also, every file contains 2 worksheets named TEMPLATE and REVISION LOG in a random order, you want the TEMPLATE worksheet.

One more thing, when returning the DIE # the entry can be in 2 formats( HK XXXX and HK xx/xx" ).
For the first scenario, return the full value( HK XXXX) for second, return (xx/xx) only.


Thank you very much and I'd really appreciate any help.

[tigeravatar]

hk4kim,

Your masterfile does not contain any keyword codes to search for. Are they supposed to be in column A starting in row 2 and going down?

[EDIT]:
Nevermind, I found the keywords (product codes). I just wasn't scrolled all the way to the left ><. Working on a solution, shouldn't take very long.

[tigeravatar]

hk4kim,

Give this a try:

Please Login or Register  to view this content.

[hk4kim]

Works great!
Just a few minor errors i found due to lack of explanation on my part:

1. when MIN is right next to the number without spacing(i.e 4.50MIN) no value returned.
2. HK-3/4", HK 11/16", returns -3/4" and 11/16". I understand this is due to spacing errors but is there a way to extract only the number portion given irregularities? (i.e HK-#/#", HK-##/##" mm, HK-#/##", HK#/#, HK##/## can all be different patterns)

I understand the importance of data normalization but for this task it is highly unlikely I apolgize

[tigeravatar]

Is MIN the only unit of measurement for the Cure Time?

[hk4kim]

yes that is correct

[tigeravatar]

Update the If Instr... block to the following:

Please Login or Register  to view this content.

[hk4kim]

Thank you!