Check If The File Exists Using VBA

Answer :

Note your code contains Dir("thesentence") which should be Dir(thesentence).

Change your code to this

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 

Use the Office FileDialog object to have the user pick a file from the filesystem. Add a reference in your VB project or in the VBA editor to Microsoft Office Library and look in the help. This is much better than having people enter full paths.

Here is an example using msoFileDialogFilePicker to allow the user to choose multiple files. You could also use msoFileDialogOpen.

'Note: this is Excel VBA code Public Sub LogReader()     Dim Pos As Long     Dim Dialog As Office.FileDialog     Set Dialog = Application.FileDialog(msoFileDialogFilePicker)      With Dialog         .AllowMultiSelect = True         .ButtonName = "C&onvert"         .Filters.Clear         .Filters.Add "Log Files", "*.log", 1         .Title = "Convert Logs to Excel Files"         .InitialFileName = "C:\InitialPath\"         .InitialView = msoFileDialogViewList          If .Show Then             For Pos = 1 To .SelectedItems.Count                 LogRead .SelectedItems.Item(Pos) ' process each file             Next         End If     End With End Sub 

There are lots of options, so you'll need to see the full help files to understand all that is possible. You could start with Office 2007 FileDialog object (of course, you'll need to find the correct help for the version you're using).

Correction to fileExists from @UberNubIsTrue :

Function fileExists(s_directory As String, s_fileName As String) As Boolean    Dim obj_fso As Object, obj_dir As Object, obj_file As Object   Dim ret As Boolean    Set obj_fso = CreateObject("Scripting.FileSystemObject")    Set obj_dir = obj_fso.GetFolder(s_directory)    ret = False    For Each obj_file In obj_dir.Files      If obj_fso.fileExists(s_directory & "\" & s_fileName) = True Then         ret = True         Exit For       End If    Next     Set obj_fso = Nothing    Set obj_dir = Nothing    fileExists = ret   End Function 

EDIT: shortened version

' Check if a file exists Function fileExists(s_directory As String, s_fileName As String) As Boolean      Dim obj_fso As Object      Set obj_fso = CreateObject("Scripting.FileSystemObject")     fileExists = obj_fso.fileExists(s_directory & "\" & s_fileName)  End Function