VBA check if file exists
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.
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
Dim strFile As String Dim WB As Workbook strFile = Trim(TextBox1.Value) If Len(strFile) = 0 Then Exit Sub Dim DirFile As String DirFile = "C:\Documents and Settings\Administrator\Desktop\" & strFile If Len(Dir(DirFile)) = 0 Then MsgBox "File does not exist" Else Set WB = Workbooks.Open(DirFile) End If