List folder and Excel Workbooks into sheet2 using msoFileDialogFolderPicker

[healey21]

Hi All,

Can anyone help with code to list folder path and workbook names using msoFileDialogFolderPicker

I have a project to complete where a user will pick a folder and then I can populate a combo in a userform with the file names.

 

[Andrew Poulsom]

Add a UserForm with a CommandButton and a ComboBox. Then try this CommandButton code:

Private Sub CommandButton1_Click()
    Dim Folder As String
    Dim FileName As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Title = "Select Folder"
        If .Show = True Then
            Folder = .SelectedItems(1)
            FileName = Dir(Folder & Application.PathSeparator & "*.xls*")
            With ComboBox1
                .Clear
                Do While Len(FileName) > 0
                    .AddItem FileName
                    FileName = Dir
                Loop
            End With
        End If
    End With
End Sub

 

[healey21]

Thank you Andrew, this fills the combo perfectly. I am also opening the workbooks so is it possible to show the path as well please.

 

[Andrew Poulsom]

Sure:

.AddItem Folder & Application.PathSeparator & FileName

 

[healey21]

Thank you once again Andrew