VBA检查文件是否存在 [英] VBA check if file exists

问题描述

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

运行时错误1004

  Dim File As String 
 File = TextBox1.Value 
 Dim DirFile As String 
 
 DirFile =C：\Documents and Settings\\ \\Administrator\Desktop\&文件
如果Dir（DirFile）=然后
 MsgBox文件不存在
 Else 
 Workbooks.Open文件名：= DirFile 
如果
  

解决方案

这样的事情

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

更新为测试该文件名是一个实际的工作簿 - 这也使初始检查冗余，除了消息的用户比Textbox是空白

  Dim strFile As String 
 Dim WB As Workbook 
 strFile = Trim（TextBox1.Value）
 Dim DirFile As String 
如果Len（strFile）= 0然后退出Sub 
 
 DirFile =C：\Documents and Settings\Administrator\Desktop\& strFile 
如果Len（Dir（DirFile））= 0然后
 MsgBox文件不存在
 Else 
错误恢复下一步
设置WB = Workbooks.Open （DirFile）
 On Error GoTo 0 
如果WB不是，那么MsgBox DirFile& 无效，vbCritical 
结束如果
  

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"

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

解决方案

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

这篇关于VBA检查文件是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！