Excel

Browse For Folder

Ease of Use

Easy

Version tested with

97, 2003 

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. 

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 FALSE if an invalid entry was chosen. Another approach to this can (by DRJ) be found at http://www.vbaexpress.com/kb/getarticle.php?kb_id=246 

Code:

instructions for use 

			
Option Explicit 
 
Function BrowseForFolder(Optional OpenAt As Variant) As Variant 
     'Function purpose:  To Browser for a user selected folder.
     'If the "OpenAt" path is provided, open the browser at that directory
     'NOTE:  If invalid, it will open at the Desktop level
     
    Dim ShellApp As Object 
     
     'Create a file browser window at the default folder
    Set ShellApp = CreateObject("Shell.Application"). _ 
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt) 
     
     'Set the folder to that selected.  (On error in case cancelled)
    On Error Resume Next 
    BrowseForFolder = ShellApp.self.Path 
    On Error GoTo 0 
     
     'Destroy the Shell Application
    Set ShellApp = Nothing 
     
     'Check for invalid or non-entries and send to the Invalid error
     'handler if found
     'Valid selections can begin L: (where L is a letter) or
     '\\ (as in \\servername\sharename.  All others are invalid
    Select Case Mid(BrowseForFolder, 2, 1) 
    Case Is = ":" 
        If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid 
    Case Is = "\" 
        If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid 
    Case Else 
        GoTo Invalid 
    End Select 
     
    Exit Function 
     
Invalid: 
     'If it was determined that the selection was invalid, set to False
    BrowseForFolder = False 
     
End Function

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
 

Test the code:

  1. In Excel press Alt + F11 to enter the VBE.
  2. Press Ctrl + G to show the Immediate window
  3. To test with no default, type the following, then press Enter: debug.print browseforfolder
  4. 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.
  5. To test with a default, type the following, then press Enter: debug.print browseforfolder("C:\temp")
  6. 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.
 

Sample File:

BrowseForFolder.zip 10.25KB 

Approved by mdmackillop


This entry has been viewed 2696 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express