Hello,

I need some help writing code that will allow me to determine if a file
exists in a directory, or not. I have actually figured out how I can do
this by specifying a single cell. However, I need to have this go
through multiple rows and return a “yes” or “no” for each row.

The amount of rows in this worksheet can vary, so it would have to be
able to determine how many rows are in the sheet also. So, starting in
cell B8 I have a path with the filename that I want to determine its
existence. In cell G8, I want “yes” or “no” to be displayed (depending
on whether the file exists or not). I want the program to then move to
the next row and use cell B9 to determine what value will be placed in
cell G9. This would continue until the last row of the sheet is
reached.

The code below will return a “yes” or “no” in cell G8 depending on
whether the file name in cell B8 exists in the directory path in cell
B5.


Function FileThere(FileName As String) As Boolean
FileThere = (Dir(FileName) "")
End Function

Function GetFileName(FullPath As String)
Dim StrFind As String
Do Until Left(StrFind, 1) = "\"
iCount = iCount + 1
StrFind = Right(FullPath, iCount)
If iCount = Len(FullPath) Then Exit Do
Loop
GetFileName = Right(StrFind, Len(StrFind) - 1)
End Function

Sub test1()
FileName = GetFileName(Range("B8").Value)
Path = Range("B5").Value
If FileThere(Path & FileName) Then
Range("G8").Select
ActiveCell.FormulaR1C1 = "Yes"
Else
Range("G8").Select
ActiveCell.FormulaR1C1 = "No"
End If
End Sub

I do not have much experience with VB code, so any assistance with this
would be much appreciated.

Thanks,
Matt


--
mattc
------------------------------------------------------------------------
mattc's Profile: http://www.excelforum.com/member.php...o&userid=29222
View this thread: http://www.excelforum.com/showthread...hreadid=489488