This is pretty simple, I got this from Excel 2003, you may need to test for later versions, as far as I know it should still work.
Sub cmdOpenFileDialog()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant
With fd
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
MsgBox "The path is: " & vrtSelectedItem
Next vrtSelectedItem
Else
End If
End With
Set fd = Nothing
End Sub
Regards
[Update]
The answer above works well but now that I am here at work I see I've been using
GetOpenFilename
method to open files that I wish to import data from.
fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen <> False Then
MsgBox "Open " & fileToOpen
End If
This is quicker because you can specify filters in the method call, the above example filters for just text files.