VBA检查文件是否存在

Question

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

运行时错误“ 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

Answer 1

像这样的东西最好使用工作簿变量来提供对打开的工作簿的进一步控制（如果需要）已更新以测试文件名是实际的工作簿-这也使初始检查变得多余，除了向用户发送消息以外，文本框为空Dim strFile As StringDim WB As WorkbookstrFile = Trim(TextBox1.Value)Dim DirFile As StringIf Len(strFile) = 0 Then Exit SubDirFile = "C:\Documents and Settings\Administrator\Desktop\" & strFileIf 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", vbCriticalEnd If

Answer 2

我使用此功能检查文件是否存在：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&nbsp; &nbsp; On Error Resume Next&nbsp; &nbsp; IsFile = ((GetAttr(fName) And vbDirectory) <> vbDirectory)End Function

Answer 3

为了检查是否存在，还可以使用（适用于文件和文件夹）：Not Dir(DirFile, vbDirectory) = vbNullString结果是True文件或目录是否存在。例：If Not Dir("C:\Temp\test.xlsx", vbDirectory) = vbNullString Then&nbsp; &nbsp; MsgBox "exists"Else&nbsp; &nbsp; MsgBox "does not exist"End If