vba - 检查文件是否存在并且 vba 认为它在那里，类型不匹配

Question

使用下面的代码，自从我尝试将其压缩为复杂的 if 时，VBA 认为文件存在时它不存在。是因为我的声明吗？以前我以这种方式订购了它(在复杂的 if 之前)并且效果很好。当我重新排列文件位置的声明时，它仍然认为文件在那里。然后我尝试将 ElseIf 部分更改为 ' <> ""' 并将其分成两个单独的 IF，但结果相同。

以正确的最终结果编辑

      If CreateObject("Scripting.FileSystemObject").FileExists(filelocation1) Then
    filelocation1 = Environ("USERPROFILE") & "\Desktop" & "\" & Format(Date, "ddmmyyyy") & ".xls"
    filelocation2 = "\\afsaztempe1na1\site\AFS-AZ-Tempe\Shared\CERTIFICATION\ProdDumpTest" & "\" & Format(Date, "ddmmyyyy") & Application.UserName & ".xls"
    Set wbI = ThisWorkbook
    Set wsI = wbI.Sheets("Production")

    If wsI.Range("A2").value = "" Then Exit Sub

    Set wbO = Workbooks.Add

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


    With wbO
        Set wsO = wbO.Sheets("Sheet1")
        ActiveWorkbook.SaveAs Filename:=filelocation1, FileFormat:=56
        wsI.Range("A1:C100").Copy
        wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        ActiveWorkbook.Save
        ActiveWorkbook.Close
    End With

    With wsI
        wsI.Range("A2:C200").ClearContents
    End With

    FileCopy Source:=filelocation1, Destination:=filelocation2
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Exit Sub
End If

 filelocation1 = Environ("USERPROFILE") & "\Desktop" & "\" & Format(Date, "ddmmyyyy") & ".xls"
filelocation2 = "\\afsaztempe1na1\site\AFS-AZ-Tempe\Shared\CERTIFICATION\ProdDumpTest" & "\" & Format(Date, "ddmmyyyy") & Application.UserName & ".xls"

If filelocation1 <> "" Then
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wbI = ThisWorkbook
Set wsI = wbI.Sheets("Production")

With wbO
    Set wbO = Workbooks.Open(filelocation1)
    If wsI.Range("A2").value = "" Then Exit Sub
    Set wsO = wbO.Sheets("Sheet1")
    wsI.Range("a2:c100").Copy
    wsO.Cells(wsO.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End With

With wb1
    Set wb1 = Workbooks.Open(filelocation2)
    Set ws1 = wb1.Sheets("Sheet1")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    wsI.Range("a2:c100").Copy
    ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End With

With wsI
    wsI.Range("A2:C200").ClearContents
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

万一

Answer 1

如果要检查文件是否存在，可以这样做:

If CreateObject("Scripting.FileSystemObject").FileExists(filelocation1) Then

此外，要从给定名称打开工作簿，您应该这样做:

Set wbO = Workbooks.Add(filelocation1)