Macro to check if files exist

[Doedtman]

Hello!
I'm trying to create a macro that will see if a list of files exist, but I can't seem to get anything to work. I have the file name in column A (starting with row 2) and the file type (.pdf,.xls, etc) in column E. All of the files are located under C:\Documents and Settings\joddoe\Desktop\Reports. I'd like to have the cell highlighted if the file doesn't exist, but if that too complicated, a simple "yes" or "no" in column F will work. Any suggestions on how I can make this work?

Thanks!
Jodi

 

[ISY]

Hallo

Code to enter into a form:

'Title:  File_Esiste
'Author:  Microsoft
'Comment: A function that lets you know if a file exists or not.
Function DoesFileExist(strFileSpec As String) As Boolean
' Return True if file specified in the
' strFilespec argument exists.
' Return False if strFileSpec is not a valid
' file or if strFileSpec is a directory.
Const INVALID_ARGUMENT As Long = 53
On Error GoTo DoesfileExist_Err
If (GetAttr(strFileSpec) And vbDirectory) <> vbDirectory Then
DoesFileExist = CBool(Len(Dir(strFileSpec)) > 0)
Else
DoesFileExist = False
End If
DoesfileExist_End:
Exit Function
DoesfileExist_Err:
DoesFileExist = False
Resume DoesfileExist_End
End Function


Sub Test()
    MsgBox DoesFileExist("C:\big_engine_rta96.JPG")
End Sub

 

[Doedtman]

Thanks for your answer. I was hoping to use something like this:

Sub Check_File()
Dim strPath As String
Dim strName As String
Dim strFile As String
Dim strCombine As String
Dim strContinue As Boolean
Dim lRowCount As Long
Dim wsData As Worksheet
Set wsData = Worksheets("Email")
lRowCount = 2
strName = wsData.Cells(lRowCount, 1).Value 'get name of file
strFile = wsData.Cells(lRowCount, 5).Value 'get file type
strCombine = strName & strFile

strContinue = True
strPath = ("C:\Documents and Settings\joddoe\Desktop\" & strName & strFile)
 
While strContinue
 
If Len(Range("A" & CStr(lRowCount)).Value) = 0 Then
strContinue = False
 
Else

If Len(Dir(strPath & (CStr(strCombine) & CStr(lRowCount))).Value) = 0 Then
Range("F" & CStr(lRowCount)).Value = "No"

Else
Range("F" & CStr(lRowCount)).Value = "Yes"
End If
End If
lRowCount = lRowCount + 1
Wend
End Sub
[\code]
 
The error  "Invalid qualifier"  keeps coming up though.  I'd really appreciate any help with fixing this error.
 
Thanks,
Jodi