How to list all text files in a directory?

List & sort files from a directory in array.

• This is the extension from my previous question. I've tried your code but I don't know why it didn't give me the expected result. I've spending days to modify the code but it didn't work. As you can see from the attached images, there are three columns of interest (Document ID, Item and File Name). I have a ready list of Document ID and Item which I wish to compare them with the File Names column. By combining these two columns (doc ID and item), would make it easy to compare witht the file names in the folder. I found this code in one of your past answer but I don't know how to modify it. Set r1 = sh.Range("B20", sh.Range("B20").End(xlDown)) For i = LBound(v) To UBound(v) s = v(icnt) For Each cell In r1 s1 = "(" & cell.Value & ")" & cell.Offset(0, 1) & ".xls" If LCase(s) = LCase(s1) Then ' lcase makes the compare case insensitive cell.Offset(0, 2).Value = s Exit For End If Next Next FYI, the 'File Name' column consists of file names that are obtained from a folder (and subfolders) path specified by user in "C11". In 'Image01' figure, the file names are not sorted thus it is difficult to identify the missing files in the folder. I have illustrated how it should look like in figure 'Image02'. As you can see the files are sorted and the entire row for the missing files are highlighted and the text are in italic. In summary, this is what I would like to do: 1) list files names in array in 'File Name' column from the specified path "C11" 2) match and sort the files found according to combination of 'Doc ID' column and 'Item' and extension files 3) the non-match files found in folder are neglected. Only list the match files. *no need to put the non-match files below the match ones as you did previously 4) Highlight the missing files (entire row). I hope my explanation is clear to you. I'm sorry if this is a little bit too much to ask for, but I really need your kind help. I'm stuck. Thank you very much in advance.

• Answer:

  Jane/Lisa/Amy, >I've tried your code but I don't know why it didn't give me the expected result. I gave you several versions of the code as you revealed more information. I don't know which version you have tried - but I suspect that not meeting expectations may be because the requirements were not as clear to me as they are to you. In your current example, you show the filenames already in column D, but unsorted. I am not sure what the purpose of that part of the image is for because your question appears to want me to get the filenames from a specific directory location and not distribute a list of files already in column D. while your image is mostly unreadable because it is too small, it seems to me that if the document ID is in the file name, then a match can be assumed without further trying to match the item as well. Is a document ID a unique identifier or does the ITEM also need to be matched? I can't see the format of the filename in your image (again, because it is too small and blurry). Is it sufficient to test if the the document ID is included in the filename (or if the document ID and the ITEM are in the filename) to declare a match? Let me know and I will try to adjust the code for you. Why do you keep changing your Name? If your Jane, why would I relate this question to Lisa; because the image is similar to one posted by Amy? It seems disingenuous to keep changing the name in your questions. Anyway, answering the above questions would assist in revising the code. OK, I assumed that Document ID is a unique identifier and wrote code to process the filelist based on that assumption. The code is tested and worked for me. Insert a new module in your workbook and paste in all the below code: Option Explicit Dim lgcurrRow As Long Dim saFileList() As String Sub putlistinArray2() Dim spath As String, sName As String Dim v As Variant, icnt As Long, icnt1 As Long, v1 As Variant Dim sh As Worksheet, r As Range, i As Long, j As Long Dim jj As Long, bFound As Boolean, r1 As Range, iloc As Long Dim cell As Range Set sh = Worksheets("FileList3") spath = sh.Range("C11") If Right(spath, 1) <> "\" Then spath = spath & "\" sName = Dir(spath & "*.*") ' bFound = False For j = 1 To icnt If InStr(1, v(j), v1(i, 1), vbTextCompare) Then bFound = True jj = j Exit For End If Next If Not bFound Then v1(i, 1) = Empty Else v1(i, 1) = v(jj) v(jj) = Empty End If Next ReDim Preserve v(1 To icnt) For i = 1 To icnt - 1 If IsEmpty(v(i)) Then j = i + 1 Do While IsEmpty(v(j)) And j icnt Then Exit Do Loop If j Next i ReDim Preserve v(1 To icnt1) r.Offset(0, 2).Value = v1 For Each cell In r.Offset(0, 2) If Len(Trim(cell)) = 0 Then cell.EntireRow.Interior.ColorIndex = 15 End If Next End Sub Public Sub luke_Linkwalker(szPath As String, _ icnt As Long) Dim saDirList() As String Dim szNewPath As String Dim szFname As String Dim i As Long, j As Long ReDim saDirList(1 To 1) saDirList(1) = "" saFileList(1) = "" szFname = Dir(szPath, vbDirectory) ' Retrieve the first entry. i = 0 j = icnt Do While szFname <> "" ' Start the loop. ' Ignore the current directory and 'the encompassing directory. If szFname <> "." And szFname <> ".." Then ' Use bitwise comparison to make sure ' szFname is a directory. If (GetAttr(szPath & szFname) And vbDirectory) = _ vbDirectory Then ' get entry only if it is directory i = i + 1 ReDim Preserve saDirList(1 To i) saDirList(i) = szFname Else ' ^Directories j = j + 1 ReDim Preserve saFileList(1 To j) saFileList(j) = szFname End If End If szFname = Dir ' Get next entry. Loop If Len(saDirList(1)) > 0 Then For i = LBound(saDirList) To UBound(saDirList) szNewPath = szPath & saDirList(i) & "\" Debug.Print szNewPath, UBound(saFileList) luke_Linkwalker szPath:=szNewPath, icnt:=UBound(saFileList) Next i End If End Sub