check files exist when cell values change

[kaffal]

hi i will like to have a macro to check whether a file exist when one of the cell values are changed.

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 66px;"><col style="width: 106px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>H</td><td>I</td></tr><tr style="height: 14px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td style="font-family: Arial; font-size: 8pt;">Directory</td><td style="font-family: Arial; font-size: 8pt;">C:\today\sales</td></tr><tr style="height: 14px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td><td style="font-family: Arial; font-size: 8pt;">File Name </td><td style="font-family: Arial; font-size: 8pt;">090309_sales.xls</td></tr><tr style="height: 14px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td><td style="font-family: Arial; font-size: 8pt;">Files found </td><td style="font-family: Arial; font-size: 8pt;">Yes </td></tr></tbody></table>
The macro will .

- go to the path , defined from cell I16
- check whether the file name I17 is available in the path
- If exist, display "yes" is I18, if not found, display "No".

And the Macro will run everytime when the cell value in I17 are changed.

 

[Andrew Poulsom]

There is a FileExists function here:

http://spreadsheetpage.com/index.php/tip/some_useful_vba_functions/

 

[kaffal]

How Can I amend the code to what I want?
how to create the path for the FileExists function to be used?
How to make the macro run whenever I17 cell value is changed

 

[Andrew Poulsom]

I amended the function slightly:

Function FileExists(fname) As Boolean
'   Returns TRUE if the file exists
    Application.Volatile
    Dim x As String
    x = Dir(fname)
    If x <> "" Then FileExists = True _
        Else FileExists = False
End Function

Use it in your worksheet like this:

=IF(FileExists(I16&"\"&I17),"Yes","No")