Check if the file exists using VBA

user_1399257 0
Viewed 482190
Sub test()

thesentence = InputBox("Type the filename with full extension", "Raw Data File")

Range("A1").Value = thesentence

If Dir("thesentence") <> "" Then
    MsgBox "File exists."
Else
    MsgBox "File doesn't exist."
End If

End Sub

In this when i pickup the text value from the input box, it doesn't work. If however, if remove "the sentence" from If Dir() and replace it with an actual name in the code, it works. Can somebody help?

vba excel
9 Answers

Very old post, but since it helped me after I made some modifications, I thought I'd share. If you're checking to see if a directory exists, you'll want to add the vbDirectory argument to the Dir function, otherwise you'll return 0 each time. (Edit: this was in response to Roy's answer, but I accidentally made it a regular answer.)

Private Function FileExists(fullFileName As String) As Boolean
    FileExists = Len(Dir(fullFileName, vbDirectory)) > 0
End Function
user_107358640

based on other answers here I'd like to share my one-liners that should work for dirs and files:

  • Len(Dir(path)) > 0 or Or Len(Dir(path, vbDirectory)) > 0  'version 1 - ... <> "" should be more inefficient generally
    • (just Len(Dir(path)) did not work for directories (Excel 2010 / Win7))
  • CreateObject("Scripting.FileSystemObject").FileExists(path)  'version 2 - could be faster sometimes, but only works for files (tested on Excel 2010/Win7)

as PathExists(path) function:

Public Function PathExists(path As String) As Boolean
    PathExists = Len(Dir(path)) > 0 Or Len(Dir(path, vbDirectory)) > 0
End Function
user_19159200
Related