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

[klpw2]

Hi all,

I need help with vba code.

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.

Please see below for the code.

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

 

[iliace]

i want it to show 2, 3, 4 in separate column under Revision as shown in the attached final result excel workbook file.

What attached workbook file? Cross post?

Look here for some functions that may give you an idea of how to get the data you need: https://technet.microsoft.com/en-us/library/ee198734.aspx

 

[arthurbr]

Heavy cross poster vba code for integrating particular information from different workbook to one workbook