Check if the file exists using VBA

Question

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?

Answer 1

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 

Answer 2

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).