Sometimes it is necessary to get a folder path from the user instead of receiving a file path.

The Excel Application class is providing a corresponding function, which is used in below method.

Source Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
' @Author - Alexander Bolte
' @ChangeDate - 2014-10-26
' @Description - showing a dialog which allows the user to pick a folder but no files.
' @Param titleStr - A String providing a title for the folder picker.
' @Param startDirectory - A String providing the directory path, which should be shown when opening the directory picker.
' @Param buttonStr - a String providing a text to be shown as the button label of the button to select a directory.
' @Returns the folder path selected by user including a backslash at end of the returned String.
Public Function getFolderPath( _
        Optional ByVal titleStr As String = "Folder selection", _
        Optional ByVal startDirectory As String = "C:\", _
        Optional ByVal buttonStr As String = "Select Directory" _
    ) As String
    
    On Error GoTo err_handle:
    
    Dim directory As String
 
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = startDirectory
        .Title = titleStr
        .ButtonName = buttonStr
        .InitialView = msoFileDialogViewList
        If .Show = -1 Then
            directory = .SelectedItems(1)
            If Right(directory, 1) <> "\" Then directory = directory & "\"
          Else
            directory = ""
        End If
    End With
    
err_handle:
    If Err.Number <> 0 Then
        Err.Clear
        directory = ""
    End If
    
    getFolderPath = directory
End Function

All three parameters are optional.

FileDialog can be used either to get a specific file path or to get a directory path. This is controlled using two constants, in this case msoFileDialogFolderPicker.

I am lazy and do not want to hand a lot of parameters to functions having to remember what I have to hand in certain cases to get the result I want. I want to call one function for one very simple purpose.

Therefore I deceided to split the same function into two methods using two different constants to achieve similar tasks. Hence the file picker dialog is build using a similar method.