Browsing folder for selecting folder using VBA in Microsoft Excel

In this article, we have created procedure which is used for displaying dialog box, which is used for browsing through folder for selecting folder.

This code can be used along with other macros where selection of folder is required at runtime.

SelectingFolder

Logic explanation

In this article, we have made reference to two API functions to display the dialog box for browsing folder.

When we select any particular folder using folder browser, dialog box return the path of the selected folder.

 

Please follow below for the code

Option Explicit

'Declaring user data type
'Used by the function GetFolderName
Private Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type

'Declaring reference to API Function
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
    Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
    Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Function GetFolderName(Msg As String) As String

'Returns the name of the folder selected by the user

Dim bInfo As BROWSEINFO, path As String, r As Long
Dim X As Long, pos As Integer
    
bInfo.pidlRoot = 0

'Type of directory to return
bInfo.ulFlags = &H1

'Display the dialog
X = SHBrowseForFolder(bInfo)

'Parse the result
path = Space$(512)

'Calling API function
r = SHGetPathFromIDList(ByVal X, ByVal path)

'Code for deleting extra spaces in the end of folder name return
If r Then
    pos = InStr(path, Chr(0))
    GetFolderName = Left(path, pos - 1)
Else
    GetFolderName = ""
End If

End Function

Sub TestGetFolderName()

Dim FolderName As String

'Calling function GetFolderName
FolderName = GetFolderName("Select a folder")

If FolderName = "" Then
    MsgBox "You didn't select a folder."
Else
    MsgBox "You selected this folder: " & FolderName
End If

End Sub

 

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube