• Home
 · 
• Microsoft Office Apps/VBA
 · 
• Browse Folder without API reference in Word, Excel in VBA

Browse Folder without API reference in Word, Excel in VBA

This is a sample code to Open Folder Browsing Windows in Word and Excel. user need not to include any special reference or API code for this purpose.

Returns
Function returns the absolute path of the folder

Side Effects
no side effect

Rate Browse Folder without API reference in Word, Excel in VBA

• 1
• 2
• 3
• 4
• 5

(6(6 Vote))

Function BrowseFolder(Optional OpenAt As Variant) As Variant
  Dim ShellApplication As Object
  Set ShellApplication = CreateObject("Shell.Application").BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
  On Error Resume Next
  BrowseFolder = ShellApplication.self.Path
  Set ShellApplication = Nothing
  Select Case Mid(BrowseFolder, 2, 1)
    Case Is = ":"
      If Left(BrowseFolder, 1) = ":" Then GoTo err1
    Case Is = "\"
      If Not Left(BrowseFolder, 1) = "\" Then GoTo err1
    Case Else
      GoTo err1
  End Select
 Exit Function

err1:
  BrowseFolder = False
End Function

Sub OpenFolder()
  Dim Response As Variant
  Response = BrowseFolder
  If Response = False Then
    MsgBox "You have selected Invalid Folder", vbInformation
  Else
    MsgBox "Selected Folder - " & Response, vbInformation
  End If
End Sub