search for specific text in a file and return a value to a cell based on the results

I want Excel 2010 to check a bunch of non-excel files in a folder without opening them for specific texts contained within, if each file contains the text then return a value to a specific cell in a worksheet
Example: there are 5 files in a folder each of which may contain the text 2009, 2010, 2011 or 2012. When each file is searched, return the matching text as a value to a cell on a worksheet. That is to say list the match down a column on a worksheet for each file.

application file.search was probably the solution to this prior to 2007, how is it possible to accomplish the above in 2010? I need full code please

I don't think it can be done without "without opening them". I have heard of using xml to read the underlying xml code and binary code readers for files stores as a raw binary file. Some programs claim to be able to convert popular binary formats (e.g. Word, Excel) to plain text and can manipulate the binary data.

Having said all, your description doesn't describe exactly what you are after and so the more experienced members (me not being one of them) won't be able to help you much. More details may help unlocking a solution for you in the future.

Hope this helps.

abousetta

sorry, I'll try to explain it better:
I know the path of each file to search,
I want to search the contents of each file for words from a list of words and whichever word is there then have excel write the same word in a cell on a worksheet.
The list of words is 2009,2010,2011 and 2012. Each file will have one of those words contained in it. Lets say Excel checks file#1 and it finds the word 2011 from the list, then it will write the word 2011 in a cell next to the file name.
Think of when you do a search with windowsXP: START>SEARCH>ALL FILES AND FOLDERS>A WORD OR PHRASE IN THE FILE. (it doesn't actually open the file, it just looks in it)

Here is some code I found that might help show what I mean except that application file.search is no longer supported:

Please Login or Register  to view this content.

Yes, after they removed FileSearch, we all suffered . Actually it's what got me interested in vba to begin with because I was using Excel 2007 at the time and the code that I found was using FileSearch. Anyways, to the best of my limited knowledge, there is no way to do this easily without having indexed the contents of the files in a database. Having said that, if you know the file types you will be searching snb has posted examples of FileSearch alternatives. Even these force you to open the files to check their contents. His method is said to be very fast, but I have never tested it so I can't confirm these claims.

Hope this helps.

abousetta

There are several alternatives to Filesearch for randomly checking files in a folder, so we were not really put out by the loss of FileSearch.

What kind of files are they? Text files? CSV files?

Whatever method is employed will open the files in some manner to examine the contents, but the types of files would inform which method would be best.

Perhaps you can provide a sample reference file or two so we can see, and your control workbook as well where you want these results recorded?

You should be able to use Windows inherent search function via vba. This is a start but I'm not an expert on windows API but wanted to put it out there if anyone wanted a challenge.

Please Login or Register  to view this content.

abousetta

So, a macro to search a list of CSV filenames in column A and put the "date" in column B based on which is found?

The macro could really collect the filenames for you, too, no need for you to even list the filenames ahead of time. The macro could do it all, it would just need a folder to check.

Post your macro that you already have that gets the filenames, we can just add to it...

Personally, I've had a bad history with Dir and so I try to use filesystemobject instead.

Have a look at this:

Please Login or Register  to view this content.

abousetta

Replace:

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

abousetta

Glad I could help out.

Good luck.

abousetta