, 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
here is code to get a list of files:
Option Base 1 Dim aFiles() As String, iFile As Integer
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
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
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
I don't know what you mean by sorting the file names by ID.