Using a Macro to search several predetermined locations to return "yes/no"

[mpricha]

I am trying to write a macro that will search an excel table that has pre-determined locations that need to be searched. For example, when the macro is run I would like it to loop thru the locations and search for any file extensions with *.xls. (later on I may need to add several extensions to search for as well). If that location has a file with that extension I would like it to return a "Yes" back in the table. Here is a dummy table that closely resembles what I'm looking at. I am looking at a table that would look through 200 locations on 5 different drives


Bank Name File Location .xls file?
Bank A H:\BankA\file (return yes or no)
Bank B P:\BankB\file (return yes or no)

[WasWodge]

Cross-posted here http://www.mrexcel.com/forum/excel-q...rn-yes-no.html

If you must X-Post...Why you should post a link
The Forum Rules

[Leith Ross]

Hello mpricha,

Welcome to the Forum!

This macro expects row 1 to hold the column headers. The macro reads the file extensions in row 1 starting with column "C". The extensions should include the asterisk, for example: *.xls. You can new extensions as needed without changing the macro code. The macro determines the number of rows with file paths automatically. If a file path does not exist then the message "Path not found" will appear under the extension columns. Copy this code and paste it into a new VBA module in your workbook.

Please Login or Register  to view this content.

[mpricha]

awesome! this worked, question... how do i add the capabilty to add extensions. Maybe I am searching 4 or 5 extensions at a time in these folders. Are all extensions in cell C1? *.xls, *.doc?? Thanks for the help!

[Leith Ross]

Hello mpricha,

Each new extension will go into a separate column. For eaxample: C1 = *.xls, D1= *.doc, etc.

[mpricha]

Leith thanks so much, this is going to save so much time at work!

[Leith Ross]

Hello mpricha,

'S e do bheatha.
(You're Welcome.)

Bliadhna mhath ùr!
(Happy New Year!)