Check if the file exists using VBA
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).