Macro to create the list of Files and Subfolders

---

Create the list of Files in a Folder

---

Sub ListFileNames()
Dim X As Object, XNC As Object
Dim Y As Integer
Set X = CreateObject("Scripting.FileSystemObject")
Set XNC = X.GetFolder(Range("B2").Value)
For Each Z In XNC.Files
Y = Y + 1
Worksheets("File Names").Cells(Y + 1, 5).Value = Z.Name
Worksheets("File Names").Cells(Y + 1, 6).Value = Z.Path
Next
End Sub

Note: File path of the folder should be placed in the cell ‘B2’ of a sheet named ‘File Names’

---

Create the list of Subfolders in a Folder

---

Sub ListFolderNames()
Dim X As Object, XNC As Object
Dim Y As Integer
Set X = CreateObject("Scripting.FileSystemObject")
Set XNC = X.GetFolder(Range("B2").Value)
For Each Z In XNC.SubFolders
Y = Y + 1
Worksheets("Folder Names").Cells(Y + 1, 5).Value = Z.Name
Next
End Sub

Note: File path of the folder should be placed in the cell ‘B2’ of a sheet named ‘Folder Names’

---

How to use codes shared here?