by Chandan Banga (1 Submission)
Category: Microsoft Office Apps/VBA
Difficulty: Intermediate
Date Added: Wed 3rd February 2021
Rating: (5 Votes)
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.
Code Returns
Function returns the absolute path of the folder
Side Effects
no side effect
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