vba code for folder name, subfolder directory, file name & revision times

klpw · #1 12-23-2015, 02:10 AM

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

klpw · #2 12-23-2015, 07:37 PM

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.

My current code is as below:

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

macropod · #3 12-24-2015, 12:31 AM

This appears to be a duplicate of: https://www.msofficeforums.com/excel...tory-file.html
Please don't post the same question multiple times. In any event, it doesn't concern Word, which is where you posted the duplicate.

Threads merged.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Revision toggle without changing revision view?	New Daddy	Word VBA	6	04-15-2014 05:59 PM
Outlook Data File subfolder not showing messages	phrankb	Outlook	0	12-24-2013 05:26 AM
Acces a subfolder as main folder over imap in outlook 2010	Bandit	Outlook	0	10-05-2013 06:23 AM
Add folder and subfolder to Favorite	ying06	Outlook	0	03-30-2012 10:41 AM
Export Subfolder ~ Now ENTIRE Archive Folder is missing? HELP!	TwinCams	Outlook	0	01-31-2011 11:24 AM