VBA 检查文件是否存在

VBA check if file exists

提问by Josephine Bautista

I have this code. It is supposed to check if a file exists and open it if it does. It does work if the file exists, and if it doesn't, however, whenever I leave the textbox blank and click the submit button, it fails. What I want, if the textbox is blank is to display the error message just like if the file didn't exist.

我有这个代码。它应该检查文件是否存在，如果存在则打开它。如果文件存在，它确实有效，但是，如果文件不存在，则每当我将文本框留空并单击提交按钮时，它就会失败。我想要的是，如果文本框为空，则显示错误消息，就像文件不存在一样。

Runtime-error "1004"

运行时错误“1004”

Dim File As String
File = TextBox1.Value
Dim DirFile As String

DirFile = "C:\Documents and Settings\Administrator\Desktop\" & File
If Dir(DirFile) = "" Then
  MsgBox "File does not exist"
Else
    Workbooks.Open Filename:=DirFile
End If

回答by brettdj

something like this

像这样的东西

best to use a workbook variable to provide further control (if needed) of the opened workbook

最好使用工作簿变量来提供对打开的工作簿的进一步控制（如果需要）

updated to test that file name was an actual workbook - which also makes the initial check redundant, other than to message the user than the Textbox is blank

更新以测试该文件名是一个实际的工作簿 - 这也使初始检查变得多余，除了向用户发送消息而不是文本框为空

Dim strFile As String
Dim WB As Workbook
strFile = Trim(TextBox1.Value)
Dim DirFile As String
If Len(strFile) = 0 Then Exit Sub

DirFile = "C:\Documents and Settings\Administrator\Desktop\" & strFile
If Len(Dir(DirFile)) = 0 Then
  MsgBox "File does not exist"
Else
 On Error Resume Next
 Set WB = Workbooks.Open(DirFile)
 On Error GoTo 0
 If WB Is Nothing Then MsgBox DirFile & " is invalid", vbCritical
End If

回答by Patrick Honorez

I use this function to check for file existence:

我使用这个函数来检查文件是否存在：

Function IsFile(ByVal fName As String) As Boolean
'Returns TRUE if the provided name points to an existing file.
'Returns FALSE if not existing, or if it's a folder
    On Error Resume Next
    IsFile = ((GetAttr(fName) And vbDirectory) <> vbDirectory)
End Function

回答by ZygD

For checking existence one can also use (works for both, files and folders):

为了检查存在，还可以使用（适用于文件和文件夹）：

Not Dir(DirFile, vbDirectory) = vbNullString

_{The result is Trueif a file or a directory exists.}

_{结果是True文件或目录是否存在。}

Example:

If Not Dir("C:\Temp\test.xlsx", vbDirectory) = vbNullString Then
    MsgBox "exists"
Else
    MsgBox "does not exist"
End If

例子：

If Not Dir("C:\Temp\test.xlsx", vbDirectory) = vbNullString Then
    MsgBox "exists"
Else
    MsgBox "does not exist"
End If

回答by matzone

Maybe it caused by Filenamevariable

可能是由Filename变量引起的

File = TextBox1.Value

It should be

它应该是

Filename = TextBox1.Value

回答by riderBill

I'll throw this out there and then duck. The usual reason to check if a file exists is to avoid an error when attempting to open it. How about using the error handler to deal with that:

我会把它扔到那里然后躲起来。检查文件是否存在的通常原因是为了避免在尝试打开文件时出错。如何使用错误处理程序来处理：

Function openFileTest(filePathName As String, ByRef wkBook As Workbook, _
                      errorHandlingMethod As Long) As Boolean
'Returns True if filePathName is successfully opened,
'        False otherwise.
   Dim errorNum As Long

'***************************************************************************
'  Open the file or determine that it doesn't exist.
   On Error Resume Next:
   Set wkBook = Workbooks.Open(fileName:=filePathName)
   If Err.Number <> 0 Then
      errorNum = Err.Number
      'Error while attempting to open the file. Maybe it doesn't exist?
      If Err.Number = 1004 Then
'***************************************************************************
      'File doesn't exist.
         'Better clear the error and point to the error handler before moving on.
         Err.Clear
         On Error GoTo OPENFILETEST_FAIL:
         '[Clever code here to cope with non-existant file]
         '...
         'If the problem could not be resolved, invoke the error handler.
         Err.Raise errorNum
      Else
         'No idea what the error is, but it's not due to a non-existant file
         'Invoke the error handler.
         Err.Clear
         On Error GoTo OPENFILETEST_FAIL:
         Err.Raise errorNum
      End If
   End If

   'Either the file was successfully opened or the problem was resolved.
   openFileTest = True
   Exit Function

OPENFILETEST_FAIL:
   errorNum = Err.Number
   'Presumabley the problem is not a non-existant file, so it's
   'some other error. Not sure what this would be, so...
   If errorHandlingMethod < 2 Then
      'The easy out is to clear the error, reset to the default error handler,
      'and raise the error number again.
      'This will immediately cause the code to terminate with VBA's standard
      'run time error Message box:
      errorNum = Err.Number
      Err.Clear
      On Error GoTo 0
      Err.Raise errorNum
      Exit Function

   ElseIf errorHandlingMethod = 2 Then
      'Easier debugging, generate a more informative message box, then terminate:
      MsgBox "" _
           & "Error while opening workbook." _
           & "PathName: " & filePathName & vbCrLf _
           & "Error " & errorNum & ": " & Err.Description & vbCrLf _
           , vbExclamation _
           , "Failure in function OpenFile(), IO Module"
      End

   Else
      'The calling function is ok with a false result. That is the point
      'of returning a boolean, after all.
      openFileTest = False
      Exit Function
   End If

End Function 'openFileTest()

回答by Andrew Prostko

Here is my updated code. Checks to see if version exists before saving and saves as the next available version number.

这是我更新的代码。在保存之前检查版本是否存在并保存为下一个可用版本号。

Sub SaveNewVersion()
    Dim fileName As String, index As Long, ext As String
    arr = Split(ActiveWorkbook.Name, ".")
    ext = arr(UBound(arr))

    fileName = ActiveWorkbook.FullName

    If InStr(ActiveWorkbook.Name, "_v") = 0 Then
        fileName = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) & "_v1." & ext
    End If

   Do Until Len(Dir(fileName)) = 0

        index = CInt(Split(Right(fileName, Len(fileName) - InStr(fileName, "_v") - 1), ".")(0))
        index = index + 1
        fileName = Left(fileName, InStr(fileName, "_v") - 1) & "_v" & index & "." & ext

    'Debug.Print fileName
   Loop

    ActiveWorkbook.SaveAs (fileName)
End Sub

回答by Excel Hero

A way that is clean and short:

一种干净而简短的方式：

Public Function IsFile(s)
    IsFile = CreateObject("Scripting.FileSystemObject").FileExists(s)
End Function

回答by Leng Keong

You should set a condition loop to check the TextBox1 value.

您应该设置一个条件循环来检查 TextBox1 值。

If TextBox1.value = "" then
   MsgBox "The file not exist" 
   Exit sub 'exit the macro
End If

Hope it help you.

希望对你有帮助。