PDA

View Full Version : vba code for excel list of folder name, subfolder directory, file name and revision



klpw
12-23-2015, 02:04 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
12-23-2015, 02:43 AM
A folder look like this:
15028
The output in Excel like this:
15029

Is anyone can help?

I appreciate all your help.

Thank you in advance.

klpw
12-23-2015, 07:19 PM
Dear All,

I might not state clearly in my previous post.

1503415035150361503715038
Please see the above 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.

mancubus
12-24-2015, 12:32 AM
in every message you posted, parent folder always changed.
why?

related with your requirement, i think, you can not get a reply with zoo-plant, john-sarah type data obfuscation. so last post of yours is very close to the desired.

if a file/folder name is meant to indicate something it has to follow a naming convention.

first read this: http://www.exadox.com/en/articles/file-naming-convention-ten-rules-best-practice

then review your chances to rename the files and folders according to your naming convention. i recommend, for example, SomeName_R10.pdf, SomeAnotherName_R3.pdf. so you can easily parse the revision number from base file name.

if yo can not, tell us how to extract the revision number from file name. can you assure that file names (when their extensions excluded) always end with letter R followed by a number (one, two, perhaps three digits)?

mancubus
12-24-2015, 03:36 AM
if all files' names end with "Rx.pdf" or "Rxy.pdf (x,y being numbers) then try below code. i added the test files i used. unzip attaches zipped file on your desktop and run the below code from attached excel file. make necessary changes to path, etc, where necessary.

i used a pivot table to see the files revision numbers.



Sub vbax_54619_Folder_File_List()

Dim oPF As Object, oSF1 As Object, oSF2 As Object, oFile As Object
Dim ParentFolderName As String
Dim iFolder As Long

ParentFolderName = "C:\Users\pwloo\Desktop\vbax_test_54619\"

Worksheets("FileFolderList").Activate
Cells(1).CurrentRegion.Clear
Range("A1").Value = "Subfolder Name"
Range("B1").Value = "Path to Sub_Subfolder"
Range("C1").Value = "File Name"
Range("D1").Value = "Revision Number"

With CreateObject("Scripting.FileSystemobject")
Set oPF = .GetFolder(ParentFolderName)
iFolder = 1
For Each oSF1 In oPF.Subfolders
For Each oSF2 In oSF1.Subfolders
For Each oFile In oSF2.Files
iFolder = iFolder + 1
Cells(iFolder, "A").Value = oSF1.Name
Cells(iFolder, "B").Value = oSF2.Path
Cells(iFolder, "C").Value = Left(.GetBaseName(oFile.Name), InStrRev(UCase(.GetBaseName(oFile.Name)), "R") - 1)
Cells(iFolder, "D").Value = Mid(.GetBaseName(oFile.Name), InStrRev(UCase(.GetBaseName(oFile.Name)), "R"))
Next oFile
Next oSF2
Next oSF1
End With

End Sub