OpenFileDialog in vba that returns the directory as a string

Question

I have been looking everywhere and I am very surprised that this is not already easily available as a function in VBA.

I need a function that when called, opens a filedialog where people can select 1 file (not more, just 1) and then the function returns the location of the file (including filename+extension) as a string.

At first i thought: " How hard can that be, I'ts really simple in VB.NET.."

Thanks in advance!

Answer 1

You mean like htis?

Sub Sample()
    Dim ofD As Object
    Dim Fil

    Set ofD = Application.FileDialog(3)

    ofD.AllowMultiSelect = False
    ofD.Show

    For Each Fil In ofD.SelectedItems
        MsgBox Fil
    Next
End Sub

The above For loop is useful if AllowMultiSelect is True

Here is another example if there is only one file.

Sub Sample()
    Dim ofD As Object
    Dim Fil

    Set ofD = Application.FileDialog(3)

    ofD.AllowMultiSelect = False

    If ofD.Show = False Then
        MsgBox "User Pressed Cancel"
    Else
        MsgBox ofD.SelectedItems(1)
    End If
End Sub