Check if a list of Files exists Macro

[cjconnor24]

Hi all,

I have a list of file paths in excel and want to use a macro to check if that actual files exists. I have it working where i can manually check the selected cell. However, i want to know if its possible to check the whole list in the one go - and for it to write the result in column A - either YES or NO.

I have attached what i've got so far - any help would be greatly appreciated.

Thanks,
C

[Richard Buttrey]

Hi,

One way:

Sub LogoExists()
    Dim x As Long
    x = 1
    Range("B2").Select

    Do While Range("B2").Cells(x, 1) <> ""
        If Dir(Range("b2").Cells(x, 1)) <> "" Then
            Range("A" & x + 1) = "Yes"
        Else
            Range("A" & x + 1) = "No"
        End If
        x = x + 1
    Loop
End Sub

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt f11 insert > module)

Sub check_file()
Dim f As String
Dim a As Integer, x As Integer, y As Integer
Cells(2, 1).Select
f = Dir("C:\Documents and Settings\c.connor\Desktop\colinVariableData\Images\" & "*.eps")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
x = Cells(Rows.Count, 1).End(xlUp).Row
y = Cells(Rows.Count, 4).End(xlUp).Row
For a = 2 To y
Cells(1, 5) = "=match(D" & a & ",A2:A" & x & ",0)"
Cells(1, 6) = "=iserror(E1)"
If Cells(1, 6) = False Then
Cells(a, 5) = "file exists"
Else
Cells(a, 5) = "No"
End If
Next a
End Sub

run the macro. it will list all files in the said folder and compares with your list and fills col E with yes or no
Ravi

[cjconnor24]

That's amazin, thanks a lot!

Don't know exactly what it does but works great!

Not sure where i'd be without this forum sometimes - thanks again!