CODEWITHSUNDEEP
vbaexcel
Sangram
Sangram

Reputation: 417

Excel macro for checking existence of file

I don't have any understanding of VBA. I have excel file which contains File path and I want to find existence of file in that location.

I tried the following but need something better than this

Sub Test_File_Exist_With_Dir()
    Dim FilePath As String
    Dim TestStr As String

    FilePath = ActiveSheet.Range("A7").Value

    TestStr = ""
    On Error Resume Next
    TestStr = Dir(FilePath)
    On Error GoTo 0
    If TestStr = "" Then
        ActiveSheet.Range("B7").Value = 0
    Else
        ActiveSheet.Range("B7").Value = 1
    End If

End Sub

Expected output 

    File path                       Existence
    C:\Users\Desktop\Excel\Jan15.txt    1
    C:\Users\Desktop\Excel\Feb15.txt    1
    C:\Users\Desktop\Excel\Mar15.txt    1
    C:\Users\Desktop\Excel\Apr15.txt    0
    C:\Users\Desktop\Excel\May15.txt    0

If I add new row to data then its existence should automatically populate.

Upvotes: 1

Views: 141

Answers (1)

R3uK
R3uK

Reputation: 14547

Yau can use this as a function directly in your workbook as a classic Excel formula, just type =File_Exist(A1) and this will work as a normal function (you can autofill next rows easily).

Public Function File_Exist(ByVal FilePath As String) As Integer
    On Error Resume Next

    Dim TestStr As String
    TestStr = Dir(FilePath)
    On Error GoTo 0

    If TestStr <> "" Then
        File_Exist = 1
    Else
        File_Exist = 0
    End If

End Function

If you want to test existence automatically and populate every time you add new row to data then you'll have to use Worksheet_SelectionChange but it'll be more difficult than this and not so useful if you have a practical function!

Upvotes: 1

Related Questions