Dear all,

I would like to create an excel sheet which shows folder name, the folder path of subfolder, the file name in the subfolder which consists of 8 combination of characters and numbers and the updated revision numbers.

I've written some codes as below and it can only shows the folder name and one of the folder name isn't appeared in the excel list. It would be highly appreciated if you could help.

Thanks.

Sub FolderNameList()

Dim iFolder As Long
Dim oFS0 As Object
Dim oFolder As Object
Dim oFldr As Object

Set oFS0 = CreateObject("Scripting.FileSystemobject")
Set oFolder = oFS0.getfolder("C:\Users\pwloo\Desktop\goal")

For Each oFldr In oFolder.Subfolders


iFolder = iFolder + 1
Cells(iFolder, "A").Value = oFldr.Name
Next oFldr

'Insert the headers for Columns'
Range("A1").Value = "Folder Name"
Range("B1").Value = "Directory of subfolder"
Range("C1").Value = "file name"
Range("D1").Value = "numbers of file"


Set oFolder = Nothing
Set oFS0 = Nothing

End Sub