Filepath检查器

Question

我正在检查文件夹中是否存在特定的文件。

例如，在我的代码中，有3个文件属于文件夹1，1个文件属于文件夹2。

我希望输出能让我知道文件“存在”还是“不存在”。

有个错误。filename3 = ActiveSheet.Range("B21").Value不存在，但是Message显示它确实存在。我认为这与我加入filepath +文件名的方式有关。

另外，有什么方法可以使代码更优雅吗？

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

在回答了功能问题之后，清理代码很容易--您只需要一些数组和循环，而不是所有这些独立的变量。

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