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.

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

Comments