Check if file exists

Hi All,

I have a sheet that has variable list of file name and location. So as its variable I don't know the length of the list as it changes.
The file location will be in column C and look like:

C:\Documents and Settings\jtb\Desktop\JB7test\jb1.xls

I would like to run down column C and check that each file exists and if not message that file XXX does not exist.

I am sure it stars like below but I am stuck here, can anyone help?

Sub first()
    For i = 1 To 10
        Cells(i, 1) = i
    Next i
End Sub

This macro display on column 'D' if file of columnd 'C' exists or does not exist:

Sub Macro1()
   Dim r As Long, lastRow As Long
   
   With ThisWorkbook.ActiveSheet
      'get last row
      lastRow = .Cells(Rows.Count, "c").End(xlUp).Row
      
      'cycle until last row
      For r = 2 To lastRow
         If Dir(.Cells(r, "c")) <> "" Then
            .Cells(r, "d") = "file exists"
         Else
            .Cells(r, "d") = "file doesn't exists"
         End If
      Next r
   End With
End Sub

Regards,
Antonio

You could possibly modify this to suit your needs, and then put it in a suitable loop.

Sub CheckIfFileExists()
    Dim strExcelFileName As String 
    strExcelFileName = "C:\My Documents\Test\Sample1.xls"

    'Check for file, exit if missing.
    If Dir(strExcelFileName) = "" Then
        MsgBox strExcelFileName & Chr(13) & Chr(13) & "Cannot be found.", _
               vbCritical + vbOKOnly, "Requested File Missing."
        Exit Sub
    End If

End Sub

Hope this helps

Hi Guys,

I am trying to mix the both but get an run time error 54 on line

If Dir(.Cells(r, "c")) <> "" Then

Post the all of the code you have "mixed", so we can understand your problem better.

I gave up the but the code is

Sub Macro99()
Dim r As Long, lastRow As Long
Dim strExcelFileName As String
    
With ThisWorkbook.ActiveSheet
      'get last row
      lastRow = .Cells(Rows.Count, "c").End(xlUp).Row
'cycle until last row
      For r = 2 To lastRow
         If Dir(.Cells(r, "c")) <> "" Then
            .Cells(r, "d") = strExcelFileName & "file exists"

         Else
            .Cells(r, "d") = strExcelFileName & "file doesn't exists"
         End If
      Next r

End Sub

Oh its run time error 52.

Sub Macro99()
Dim r As Long, lastRow As Long
Dim strExcelFileName As String
    
With ThisWorkbook.ActiveSheet
      'get last row
      lastRow = .Cells(Rows.Count, "c").End(xlUp).Row
'cycle until last row
      For r = 3 To lastRow
         If Dir(.Cells(r, "c")) <> "" Then
            .Cells(r, "d") = strExcelFileName & "file exists"

         Else
            .Cells(r, "d") = strExcelFileName & "file doesn't exists"
         End If
      Next r
      End With

End Sub

As in the colon next to the C?
C:\Documents and Settings\jtb\Desktop\JB7test\jb1.xls
Then how can you put a location in?

To add to rompers' last post

From Help

Excel workbook file names cannot include characters with mathematical formula or wildcard characters. For example, inclusion of a square bracket will corrupt the worksheet names used in the workbook. It is possible for Internet Explorer to include invalid characters automatically in some cases when the file is downloaded from a Web site.
To avoid this error, do not add invalid characters to the name of workbooks or worksheets in Excel.
It is also recommended to not use double quotes, parenthesis, or other characters as part of a file name or worksheet name. These characters may be considered "string delimiters" or special operators for the language your system is configured to use. This is especially true if you are using any Visual Basic for Applications (VBA) programming or string handling methods with these files. Common string delimiters for US English are:
" (straight quotation mark)
' (single straight quotation mark)
~ (tilde)
[ ] (brackets)
{ } (braces)
( ) parentheses
& (ampersand)
# (number sign)
* (asterisk)
: (colon)
? (question mark)
| (vertical bar)
+ (plus sign)
> (greater than)
< (less than)
. (period)
\ (backslash)

Cheers