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.PublicFunctiongetFolderPath(_OptionalByValtitleStrAsString="Folder selection",_OptionalByValstartDirectoryAsString="C:\",_OptionalByValbuttonStrAsString="Select Directory"_)AsStringOnErrorGoToerr_handle:DimdirectoryAsStringWithApplication.FileDialog(msoFileDialogFolderPicker).InitialFileName=startDirectory.Title=titleStr.ButtonName=buttonStr.InitialView=msoFileDialogViewListIf.Show=-1Thendirectory=.SelectedItems(1)IfRight(directory,1)<>"\"Thendirectory=directory&"\"Elsedirectory=""EndIfEndWitherr_handle:IfErr.Number<>0ThenErr.Cleardirectory=""EndIfgetFolderPath=directoryEndFunction
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.