How to prompt for a folder path and store in a cell \

[psycoperl]

Happy July!

I am working on a project that I am going to be using for bulk renaming and organizing of files. How can I prompt the user to select a folder that contains the files that we are going to be working on and store that value in a cell so that I can use it in formulas?

I am familiar with VBA in the MS Access Context but not in Excel.

 

[Domenic]

For the Window's version of Excel, you can use the FileDialog object to prompt the user to select a folder...

Sub test()

    Dim userSelectedFolder As String
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .ButtonName = "Select"
        .InitialFileName = Application.DefaultFilePath & "\" 'change the starting folder as desired
        .Title = "Select a folder"
        If .Show = 0 Then Exit Sub
        userSelectedFolder = .SelectedItems(1) & "\"
    End With
    
    ThisWorkbook.Worksheets("Sheet1").Range("A2").Value = userSelectedFolder 'change the destination cell as desired
    
End Sub

Hope this helps!

 

[psycoperl]

For the Window's version of Excel, you can use the FileDialog object to prompt the user to select a folder...

Sub test()

    Dim userSelectedFolder As String
   
    With Application.FileDialog(msoFileDialogFolderPicker)
        .ButtonName = "Select"
        .InitialFileName = Application.DefaultFilePath & "\" 'change the starting folder as desired
        .Title = "Select a folder"
        If .Show = 0 Then Exit Sub
        userSelectedFolder = .SelectedItems(1) & "\"
    End With
   
    ThisWorkbook.Worksheets("Sheet1").Range("A2").Value = userSelectedFolder 'change the destination cell as desired
   
End Sub

Hope this helps!

Thank you. I got something close to that.

Sub SelectDropboxPath_Click()

Worksheets("ToolSettings").Range("B2").Value = FileDialog(4, False, "Select Dropbox Location")

End Sub

As I already had a FileDialog VBA Function that I use within MS Access so I just needed to get it into the cell.

' Allows you to open any type of file dialog
' 1 - open      (NOT SUPPORTED IN ACCESS)
' 2 - save as   (NOT SUPPORTED IN ACCESS)
' 3 - file picker
' 4 - folder picker
' Filters is a 2D array of filter options.  Example: ReDim filters(0, 0 To 1)
'                                                    filters(0, 0) = "All Files"  - Description
'                                                    filters(0, 1) = "*.*"        - Filter
' separate filter conditions with a ; to include multiple filters in one option like "*.xlsx; *.xls; *.xlsm" '

Public Function FileDialog(Dialog As Integer, _
                    Optional Multi As Boolean = False, _
                    Optional Title As String = "File Dialog", _
                    Optional filters As Variant, _
                    Optional FilePath As String) As Variant
    Dim Dlg As Object

    Set Dlg = Excel.Application.FileDialog(Dialog)
    Dim i As Integer
    With Dlg
        .Title = Title
        .InitialFileName = FilePath
        If Dialog = 3 Then
            If Not IsMissing(filters) Then
                '.filters.Clear
                For i = LBound(filters, 1) To UBound(filters, 1)
                    .filters.Add filters(i, 0), filters(i, 1)
                Next
            Else
                .filters.Clear ' filters will be saved from last open
                .filters.Add "All Files", "*.*"
            End If
        End If
        .AllowMultiSelect = Multi
        Dim varMulti As Variant
        i = 0
        Dim selectedFiles() As String
        ' if true user picked a file, if false user clicked cancel
        If .Show Then
            If .AllowMultiSelect And .SelectedItems.Count > 1 Then
                ReDim selectedFiles(0 To .SelectedItems.Count - 1)
                For Each varMulti In .SelectedItems
                   selectedFiles(i) = .SelectedItems(i + 1)
                   i = i + 1
                Next
                FileDialog = selectedFiles
            Else
                FileDialog = .SelectedItems(1)
            End If
        Else
            Exit Function
        End If
    End With

    'FileDialog = Dlg.selecteditems(1)
End Function