Excel VBA Filepath Checker

Question

I'm trying to write a VBA to check if particular files exists in folders.

For example in my code, there are 3 files that belong to folder 1, and 1 file belong to folder 2.

I want the output to be let me know if the file "exists" or "does not exist"

There is however an error that I don't know how to fix, that is: filename3 = ActiveSheet.Range("B21").Value does not exist, but the Message Box displays that it does. I'm thinking its to do with the way I joined the filepath + filename, but not sure how to fix.

Also, is there any way to make the code more elegant?

Any help would be highly appreciated!! Thanks!!

Sub InputChecker()
    Dim filepath As String
    Dim filename As String
    Dim result1 As String
    Dim fullpath As String
    
    filepath1 = ActiveSheet.Range("H14").Value
    filename1 = ActiveSheet.Range("H15").Value
    filename2 = ActiveSheet.Range("H16").Value
    filename3 = ActiveSheet.Range("B21").Value

    filepath2 = ActiveSheet.Range("H18").Value
    filename4 = ActiveSheet.Range("H19").Value

    Dim fullpath1 As String
    fullpath1 = filepath1 & filename1
        If Dir(fullpath1) = VBA.Constants.vbNullString Then
            result1 = filename1 + ", File does not exist"
        Else
            result1 = filename1 + ", File exist"
        End If

    Dim fullpath2 As String
    fullpath2 = filepath1 & filename2
        If fullpath2 = VBA.Constants.vbNullString Then
            result2 = filename2 + ", File does not exist"
        Else
            result2 = filename2 + ", File exist"
        End If

    Dim fullpath3 As String
    fullpath3 = filepath1 & filename3
        If fullpath3 = VBA.Constants.vbNullString Then
            result3 = filename3 + ", File does not exist"
        Else
            result3 = filename3 + ", File exist"
        End If

    Dim fullpath4 As String
    fullpath4 = filepath2 & filename4
        If fullpath4 = VBA.Constants.vbNullString Then
            result4 = filename4 + ", File does not exist"
        Else
            result4 = filename4 + ", File exist"
        End If

    MsgBox (result1 & vbNewLine & result2 & vbNewLine & result3 & vbNewLine & result4)
    Cells(18, 3).Value = Format(Now, "yyyy-MM-dd hh:mm:ss")

End Sub

Answer 1

Having answered the functionality question, cleaning up the code is easy - you just need some arrays and loops rather than all those separate variables.

Sub InputChecker()
    Dim Sht As Worksheet: Set Sht = ActiveSheet
    'String arrays for files, folders and fullpaths:
    Dim strFile(1 To 4, 1 To 2) As String, strPath(1 To 2) As String, result As String 'we only need the one result string
    
    With Sht
        strPath(1) = .Range("H14").Value
        strPath(2) = .Range("H18").Value
        
        'use strFile(x, 1) for file names
        strFile(1, 1) = .Range("H15").Value
        strFile(2, 1) = .Range("H16").Value
        strFile(3, 1) = .Range("B21").Value
        strFile(4, 1) = .Range("H19").Value
        
        'use strFile(x, 2) for fullpaths
        For a = 1 To 4
            strFile(a, 2) = strPath(IIf(a < 4, 1, 2)) & strFile(a, 1)
        Next
        
        'Now loop through to build the result string
        For a = 1 To 4
            'file name gets added first each time
            result = result & strFile(a, 1) & "; "
            If Len(Dir(strFile(a, 2))) = 0 Then
                result = result & "File does not exist" & vbNewLine
            Else
                result = result & "File exists" & vbNewLine
            End If
        Next
        
        'That leaves a spare line break at the end of result string, remove it;
        If Right(result, 1) = vbNewLine Then result = Left(result, Len(result) - 1)

        'Message box (I added button formatting and a title)
        MsgBox prompt:=result, Buttons:=vbOKOnly + vbInformation, Title:="Result"

        .Cells(18, 3).Value = Format(Now, "yyyy-MM-dd hh:mm:ss")

    End With 'sht

End Sub