Browse folder and list all the file names in sheet

[jayymehta]

Hi, I made a user form consisting of a Command Button "Browse". When a user clicks on it, a dialog box appears to search for a folder. Now when a folder is selected, I want that the Name of all the Files inside that selected Folder should be copied in my excel sheet.
I tried coding for the same, here's my try:

Private Sub CommandButton1_Click()Dim fName As Object
Dim fObjct As Object
With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show = -1 Then
        fName = .SelectedItems(1)
    End If
End With


If fName <> "" Then
    For Each fObjct In fName
        Sheets("Sheet5").Cells(i + 1, 5) = fObjct.Name
        i = i + 1
    Next fObjct
End If
End Sub

I tried running this code, but it shows error in the line

fName = .SelectedItems(1)

"Object Variable or With variable not set".
Can anyone help me code this?

 

[Fluff]

Hi & welcome to the board.
How about this

Sub GetFileNames()

   Dim FldrPth As String
   Dim fName As String
   Dim Fldr As Object
   Dim Cnt As Long
   
   Set Fldr = Application.FileDialog(4)
   With Fldr
      .title = "Select a Folder"
      .AllowMultiSelect = False
      If .Show <> -1 Then Exit Sub
      FldrPth = .SelectedItems(1)
   End With
   
   FldrPth = FldrPth & "\"
   fName = Dir(FldrPth & "*")
   Do While Len(fName) > 0
      Cnt = Cnt + 1
      Sheets("Sheet1").Range("E" & Cnt).Value = fName
      fName = Dir
   Loop

End Sub

 

[jayymehta]

Hi, This worked like a charm! Thanks a lot! Just a single query: what does this "4" represents in

Set Fldr = Application.Filedialog(4)

??

 

[Fluff]

Glad to help & thanks for the feedback.

The 4 represents msoFileDialogFolderPicker but it means you don't need to set a reference in the VBE to the Microsoft Office xx.0 Object Library