vba code help for folder name, file location, file name and revision times

[klpw]

Hi all,

I need help with my vba code.

Please see the attached the folder look, subfolder look and the final result that I intend to get.

I would like to make a master list which shows the main folder name, the location of the folder name for the file, the file name which only consists of 8 combination of both characters and numbers (*ignore anything after 8 digits, For instance, if it's 4FH89JL6R3, i only awnt 4FH89JL6) as well as the revision times (For instance, every file will have the file number with revision times such as R2, R3, R4, i want it to show 2, 3, 4 in separate column under Revision as shown in the attached final result excel workbook file. Some of the file might have file number without revision times such as 1UKL456C R1 or 8IL30C3Q_tif0178, for these files, i want it to show the word 'error' or anything in the cell so I know the file name is not followed the rule like the one previously (4FH89JL6R3), hence, I can know which folder I need to go in and amend. I don't need to open one by one manually). At the end. when the user open the sheet, I hope that the person can type in the name of the folder, and click the OK button. For instance, the person type 'goal' in the message box, the access list for all the folders and files in 'goal' folder will show up. So that person knows which to amend.

Hope to hear from you soon.

Thanks in advanced.

Below is the code that I've created.

Sub FolderNameList()
Dim iFolder As Long
Dim oFS0 As Object
Dim oFolder As Object
Dim oFldr As Object
Dim fl As Object
Dim flNames As Object
Dim fldr 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 + 1, "A").Value = oFldr.Name
Next oFldr

    'Insert the headers for Columns'
    Range("A1").Value = "Master Folder"
    Range("B1").Value = "Location"
    Range("C1").Value = "file name"
    Range("D1").Value = "numbers of file"
 
Set oFolder = Nothing
Set oFS0 = Nothing


End Sub

 

[Marc L]

Hi !

Inside your folder loop,
create an inner loop for the Files (inner folder collection) …

FileSystemObject external library is OK for few folders & files
but in case of many, better is to use VBA inner function Dir
which runs much more faster …
​