CODEWITHSUNDEEP
vbaexcelfilesearch
JamX
JamX

Reputation: 11

VBA file exists from column

It's my first time writing macros and I am not able to see why this does not work

   Sub fillColumnStatus()
 Dim LPath As String
 Dim LExtension As String
 Dim SRow As Integer

 SRow = 4
 Path = Application.ActiveWorkbook.Path
 LPath = "C:\Users\Files"
 LExtension = ".pdf"


 While (SRow < 1131)
         If Len(Range("A" & CStr(SRow)).Value) = 0 Then
            Range("B" & CStr(SRow)).Value = ""
        Else
            If Len(Dir(LPath & Range("A" & CStr(SRow)).Value & LExtension)) = 0 Then
            Range("B" & CStr(SRow)).Value = "Check"
            Else
            Range("B" & CStr(SRow)).Value = "No"
            End If
        End If
     SRow = SRow + 1
  Wend
End Sub

Empty cells are correctly displayed, but I got only not found for all file.

Thanks,

Upvotes: 0

Views: 80

Answers (1)

Doug Coats
Doug Coats

Reputation: 7107

Sub DougsLoop()
    Dim rCell As Range, rRng As Range
    dim path as string, fileExt as string
    path ="C:\Users\Files\"
    fileExt = ".pdf"
    Set rRng = ThisWorkbook.Sheets("yoursheetname").Range("h1:h1131")
    For Each rCell In rRng.Cells
        if rcell.value = "" then
            'do nothing
        else
            if Dir(path & rcell.value & fileExt) <> "" Then
                'fileexists
                rcell.offset(0,3).value = "exists"
            else
                'not found
                rcell.offset(0,3).value = "doesnt exist"
            end if
        end if 
    Next rCell
End Sub

Upvotes: 1

Related Questions