keyword search in multiple file names

[hk4kim]

Hi, I was wondering if there is a way to search for a keyword(6 digit number) in file names from a folder containing a huge list of files and if the match is found then return the value in the D2 cell of that file. For example:

A
123456
234567
345678
.
.
.

Basically, search for the codes in column A and return the D2 cell value in that file to in Column B.
Typically, file's name is something like "QAE 7927 123456-HK1575-800345 Ext Insp.xls." And they are all in one folder. Help is greatly appreciated.

[hk4kim]

quick note, the returning value in column B is always going to be obtained from the D2 cell of the relevant file

[tigeravatar]

hk4kim,

Give this a try:

Please Login or Register  to view this content.

[hk4kim]

As I am not too familiar with how to apply VBA, I will just give you the spreadsheet.

The keywords are listed in column A and the returning values should be listed in column B.
In case the folder directory is needed it is: \\Fps1ids\hmkqc\Inspection_Test Sheet\QAE

Thank you in advance! I really appreciate your help.

[tigeravatar]

hk4kim,

Updated code:

Please Login or Register  to view this content.

How to use a macro:

1. Make a copy of the workbook the macro will be run on
   • Always run new code on a workbook copy, just in case the code doesn't run smoothly
   • This is especially true of any code that deletes anything
2. In the copied workbook, press ALT+F11 to open the Visual Basic Editor
3. Insert | Module
4. Copy the provided code and paste into the module
5. Close the Visual Basic Editor
6. In Excel, press ALT+F8 to bring up the list of available macros to run
7. Double-click the desired macro (I named this one tgr)

[hk4kim]

It seems to work for most entries but I did find a few entries that didnt work.
Im gonna try to go over the codes myself tonight to see what you have done(if you can generiously explain some of the key aspects in your function that would be great too!)

If I may ask one additional feature to the code, in the matching file contains another set of keyword named "kg/ft". and just one row below that contains the mass in number (e.g 0.150-0.160). Obviously this is a range of the mass but if i wanted the returning value to be the midpoint of this range,(in the eg is 0.155) would it be possible to do this? (To summarize, instead of returning the D2 value in each file, find the location of kg/ft, go 1 row below which gives the range of numbers, and return the mid point of that range)

Once again, I really appreciate the effort you are going through to assist me with this. It means a lot!

[tigeravatar]

Can you provide a sample of the information that would be pulled? What you've described is kind of vague, and makes troubleshooting/adjusting rather difficult.

[hk4kim]

So the attached is one of the files in my folder and as you can see from its name, it contains the 6 digits code i am looking for.(896915)

So using your code, I want to record the midpoint value of the entry just under kg/ft cell (0.148-0.155 or Cell R14). However, as i told you this value will not always be in cell R14 so the only way to figure out the location is to locate "kg/ft" and then go one row below that. Hope this clarifies.

One more thing, the format of the number will always be the same( so 0.148-0.155 is what every file has although the number might be different.

Essentially, the returning value should go in the column I of the spreadsheet i posted yesterday.

Thank you!

[tigeravatar]

Updated code:

Please Login or Register  to view this content.

[hk4kim]

arrDataB(DataIndex) = .Sheets(1).Range("D2").Text

Intersect(rngKeys.EntireRow, rngKeys.Parent.Columns("B")).Value = Application.Transpose(arrDataB)


Are these two lines needed? I dont see anywhere that invovles "D2" and column "B"

[tigeravatar]

hk4kim,

arrDataB(DataIndex) = .Sheets(1).Range("D2").Text
Intersect(rngKeys.EntireRow, rngKeys.Parent.Columns("B")).Value = Application.Transpose(arrDataB)
Are these two lines needed? I dont see anywhere that invovles "D2" and column "B"

From post #2 in this thread, your clarification post:

quick note, the returning value in column B is always going to be obtained from the D2 cell of the relevant file

That's why I kept the D2 going to column B. Is that no longer the case?

[hk4kim]

Oh sorry, I should have clarified, they are not needed

[tigeravatar]

in that case you can simply delete all lines of code that contain arrDataB

[hk4kim]

In the first file of colum D, the asterisks are the ones that should have the non-empty
entry but didnt work. So I'm attaching some of the relevant files for you to check.

This is the revised code that I've used.

Please Login or Register  to view this content.

[hk4kim]

Sorry let me clarify, the entires that are in column D are actually in column I. I was simplifying the data for you that when i was copy and pasting it went to the wrong column. But please disregard the column D comment they should technically be in column I

[tigeravatar]

The problem is that the sheet the code should be looking in isn't always the first sheet. So because the code is looking in Sheets(1), it isn't finding the data correctly.
Are the sheets the code needs to search always named the same?

[hk4kim]

Yes its always under the name "TEMPLATE"

[tigeravatar]

Updated code:

Please Login or Register  to view this content.

[hk4kim]

It seems like some of the sheets have space after "TEMPLATE" so it is not recognizing (for example "TEMPLATE "). Could you possibly perform another keyword search for this instead of the string?

[tigeravatar]

You should really normalize the data entry to avoid stuff like this.
Anyway, going through the sheets to find one with Template in the name is easy enough:

Please Login or Register  to view this content.

[hk4kim]

Thank you for keeping me up this far I don't know how to thank you enough.

One more thing though,some of the files that I am trying to access are password protected and as I run the macro, it asks me if I want to view it as "read only". So when I do that, it seems like the entry doesn't get recorded.

Do you know how to bypass this or possibly is there a way to account "Read only" into the codes?

[tigeravatar]

You can simply set DisplayAlerts to False and then back to True later. I made a couple of the test files set to read only and ran it and it pulled the data just fine

Please Login or Register  to view this content.

[hk4kim]

Some but not all of these files are password protected. After applying your codes to set display to false. This still appears.

And just one more thing, you know how the keyword is "kg/ft", where can I add "OR" conditions to account for slightly different entires? (i.e KG/FT, KG / FT)

sorry for taking too much of your time

[tigeravatar]

Let me give you some advice: Next time, put all of your requirements in your original post, lol. All of these changing req's is really discouraging because everytime I think you're covered, something new pops up.

As for the password prompt, you should be able to bypass that and open the file as read only by updating this line:

Please Login or Register  to view this content.

To be this instead:

Please Login or Register  to view this content.

If you still get the password prompt, that means the file requires a password to even be opened, and you will need to type in the password.

As for the second, searching for alternate spellings of kg/ft, update this line:

Please Login or Register  to view this content.

To be this instead:

Please Login or Register  to view this content.

[hk4kim]

ya sorry about that, I know you were very confused and so was I haha.

the ignorereadonly recommnedation doesnt seem to work.

I think the problem is that this message pops up even before accessing the file to start running the code

[hk4kim]

ReadOnly:=True would this work?

[tigeravatar]

The True by itself is the ReadOnly property (showing that it has been set to true):

Please Login or Register  to view this content.