MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Search

Posted by RoB on November 07, 2001 3:22 PM

I would like to make a macro in excel which searchs for an excel file the user specifies. I would like the search to return any files that have the first 3 letters matching what the user specified, then list all the matches in a worksheet. I'm not sure how to ask excel to get the first 3 letters of the file name.

Also, I would like the search to look in sub directories. I have the Main directory S:\FileServer\Excel\Save FILES In Here\YEAR 2001\

In this directory is a set of 12 more directories, one for each month of the year, ie: 01-00. AND in each month, there are 3 more directories with the Names "Dennis", "Jim", and "Walt". I would like the search to return ALL the files in all (36) of the directories. I haven't really started writing the macro yet, mainly because I'm not sure where to start :)

Any help in getting me started would be greatly appreciated. Thanks.

Posted by Damon Ostrander on November 08, 2001 10:44 PM

Hi RoB,

Check out the VBA helps for the FileSearch Object. There are several examples there that should help you through it.