Select folder names (again)

 2009-04-02    Files & Folders    0    91

This is an update or addition to the old article about the same topic.
With the function below it is possible to let the user select a folder. The function returns the complete path to the selected folder and you can use that information in your macro to e.g. save a document or read input files.

Function BrowseForFolder(Optional ByVal strCaption As String = vbNullString, _
    Optional strInitialFolder As String = vbNullString) As String
Dim strFolder As String
    strFolder = vbNullString
    If Len(strInitialFolder) = 0 Then
        strInitialFolder = CurDir
    End If
    If Len(strCaption) = 0 Then
        strCaption = "Select a folder:"
    End If
    On Error Resume Next
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = strCaption
        .InitialFileName = strInitialFolder
        If .Show Then
            strFolder = .SelectedItems(1)
        End If
    End With
    On Error GoTo 0
    BrowseForFolder = strFolder
End Function
You can use the function like this:

Sub ExampleBrowseForFolder()
Dim strTargetFolder As String, strTargetFile As String
    strTargetFolder = BrowseForFolder("Please select a target folder:", "C:\My Documents\")
    If Len(strTargetFolder) = 0 Then Exit Sub ' no folder selected
    If Right$(strTargetFolder, 1) <> Application.PathSeparator Then
        strTargetFolder = strTargetFolder & Application.PathSeparator
    End If
    strTargetFile = strTargetFolder & "Important Report " & Format(Date, "yyyy-mm-dd") & ".xls"
    If Len(Dir(strTargetFile)) = 0 Then
        ActiveWorkbook.SaveAs strTargetFile
    Else
        MsgBox "The target file exist already!", vbInformation
    End If
End Sub


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.