Sort file names according to the matched ids.

Question

, how are you? Thank you very much for your help and guide. I managed to solve the loop problem already.

Anyway, I have some other questions and this is the extension from my previous question before.

Well, I have another column added, called "FILE NAME" and this column will list all the file names in a folder. Referring to the image, I'd like to know how to:

1. List all the file names from a folder including the subfolders (if any) from a given directory? I'd like to list only the file names with the extension of .doc, .xls and .EAP (user will need to input the path in a cell; Eg. in "C9").

2. Sort the file names under the 'FILE NAME' column according to the matched IDs in 'ID' colum? Eg. of file name: (AX2010-SC-0001)John

Let me know if there is any part that you don't understand, and

Best wishes, Ow

Answer

Ow

here is code to get a list of files:

Option Base 1 Dim aFiles() As String, iFile As Integer

Sub ListAllFilesInDirectoryStructure()

 Dim Counter As Integer 
 iFile = 0 
 ListFilesInDirectory "c:\test\"  ' change the top level as you wish 

 For Counter = 1 To iFile 
   Worksheets("Sheet1").Cells(Counter, 1).Value = aFiles(Counter) 
 Next 

End Sub

Sub ListFilesInDirectory(Directory As String)

 Dim aDirs() As String, iDir As Integer, stFile As String 

 ' use Dir function to find files and directories in Directory 
 ' look for directories and build a separate array of them 
 ' note that Dir returns files as well as directories when vbDirectory 

specified

 iDir = 0 
 stFile = Directory & Dir(Directory & "*.*", vbDirectory) 
 Do While stFile <> Directory 
   If Right(stFile, 2) = "\." Or Right(stFile, 3) = "\.." Then 
     ' do nothing - GetAttr doesn't like these directories 
   ElseIf GetAttr(stFile) = vbDirectory Then 
     ' add to local array of directories 
     iDir = iDir + 1 
     ReDim Preserve aDirs(iDir) 
     aDirs(iDir) = stFile 
   Else 
     ' add to global array of files 
     iFile = iFile + 1 
     ReDim Preserve aFiles(iFile) 
     aFiles(iFile) = stFile 
   End If 
   stFile = Directory & Dir() 
Loop 

 ' now, for any directories in aDirs call self recursively 
 If iDir > 0 Then 
   For iDir = 1 To UBound(aDirs) 
     ListFilesInDirectory aDirs(iDir) & Application.PathSeparator 
   Next iDir 
 End If 

End Sub

I don't know what you mean by sorting the file names by ID.