VBA Express : Multiple Apps - Browse For Folder (using FileDialog method)

Multiple Apps

Browse For Folder (using FileDialog method)

Ease of Use

Easy

Version tested with

Office 2002+

Submitted by:

Ken Puls

Description:

This function will allow a user to Browse for a folder (at a user defined starting point if desired.) It is easy to use, as the code requires no API's or special references. This function will NOT work with Office 97 or Office 2000.

Discussion:

There may be times when a user needs to point a procedure to a particular folder, say to open of save a file from/to. This macro allows the user to browse to the folder, and returns the full file path to the folder selected, or an empty string if cancelled. This is very similar in effect to http://vbaexpress.com/kb/getarticle.php?kb_id=284, but with one important difference. Unlike the other entry, you CAN browse above the optional "OpenAt" directory.

Code:

instructions for use

			Function GetFolderName(Optional OpenAt As String) As String 
    Dim lCount As Long 
     
    GetFolderName = vbNullString 
     
    With Application.FileDialog(msoFileDialogFolderPicker) 
        .InitialFileName = OpenAt 
        .Show 
        For lCount = 1 To .SelectedItems.Count 
            GetFolderName = .SelectedItems(lCount) 
        Next lCount 
    End With 
End Function

How to use:

Copy above code.
In application press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Choose Insert -> Module.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save document before any other changes

Test the code:

In application press Alt + F11 to enter the VBE.
Press Ctrl + G to show the Immediate window
To test with no default, type the following, then press Enter: ?getfoldername
You should be prompted with the browse window. Upon completion, it should type the name of the folder you selected in the Immediate window under what you typed above (unless you clicked cancel, where you'll get a blank line).
To test with a default, type the following, then press Enter: ?getfoldername("C:\temp")
You should be prompted with the browse window already in the "Temp" folder. Upon completion, it should type the name of the folder you selected in the Immediate window under what you typed above (unless you clicked cancel).

Sample File:

GetFolder.zip 9.27KB

Approved by mdmackillop

This entry has been viewed 209 times.