VBA to check directories for matching files

[jarm]

In my head this sounded quite simple, but I am quickly discovering I have more questions on how to proceed than I initially thought. Such as, how do I loop it through several directories? How do I get it to not include LogNumber "1" in LogNumber "1001"? etc

I will try to keep it simple and I can change cell and folders etc later.

Let's say, I have a sheet with the table as such in columns A and B, with the headers on row 1;

Log Number	Document Tracker
1001	NBI
101001	Authorized
2001	NBI
202001	Authorized
2003	Awaiting Check
3004	Rejected

I have files in several folders, in this case, to reflect the table.

"1001 Supplier A.pdf" is in folder "C:\Documents\Files\NBI"

"101001 Supplier A.pdf" is in folder "C:\Documents\Files\Authorized"

"2001 Supplier B.pdf" is in folder "C:\Documents\Files\NBI"

"202001 Supplier C.pdf" is in folder "C:\Documents\Files\Authorized"

"2003 Supplier B.pdf" is in folder "C:\Documents\Files\Awaiting Check"

"3004 Supplier F.pdf" is in folder "C:\Documents\Processed\Rejected"

I would like the vba script to search through the folder locations above for a file containing a matching Log Number. Then, if such a file exists, it will put into Document Tracker column some relevant text, not necessarily the folder name. So if its in folder "NBI" it says "NBI" in the relevant cell.

Any ideas?

 

[DanteAmor]

How do I get it to not include LogNumber "1" in LogNumber "1001"? etc

I identify the following patterns:
1. Your examples are "pdf" files.
2. The file name always starts with the log number, followed by a space.
"1001 Supplier A.pdf"
With these patterns, the macro creates an index (Dictionary) to identify the log numbers and not confuse "1" with "1001".
-----------
Try the following macro:
In this line of code write the initial folder: sPath = "C:\trabajo\" 'intital folder

Copy all the code into a module:

Dim xfolders As New Collection

Sub check_directories_for_matching_files()
  Dim c As Range
  Dim sPath As String
  Dim xfold As Variant, arch As Variant
  Dim lognum As String, pfold As String
  Dim dic As Object
  
  sPath = "C:\trabajo\"   'intital folder
  Set dic = CreateObject("Scripting.Dictionary")
  
  xfolders.Add sPath
  Call AddSubDir(sPath)
  
  For Each xfold In xfolders
    arch = Dir(xfold & "\" & "*.pdf")
    Do While arch <> ""
      lognum = Split(arch, " ")(0)
      pfold = Trim(Mid(xfold, InStrRev(xfold, "\") + 1, Len(xfold)))
      dic(lognum) = pfold & "|" & xfold & "|" & arch
      arch = Dir()
    Loop
    arch = ""
  Next

  Range("B2:B" & Rows.Count).ClearContents
  For Each c In Range("A2", Range("A" & Rows.Count).End(3))
    If dic.exists(c.Text) Then
      c.Offset(, 1).Value = Split(dic(c.Text), "|")(0)
    End If
  Next
End Sub

Sub AddSubDir(lPath As Variant)
  Dim SubDir As New Collection, DirFile As Variant, sd As Variant
  If Right(lPath, 1) <> "\" Then lPath = lPath & "\"
  DirFile = Dir(lPath & "*", vbDirectory)
  Do While DirFile <> ""
    If DirFile <> "." And DirFile <> ".." Then
      If ((GetAttr(lPath & DirFile) And vbDirectory) = 16) Then
        SubDir.Add lPath & DirFile
      End If
    End If
    DirFile = Dir
  Loop
  For Each sd In SubDir
    xfolders.Add sd
    Call AddSubDir(sd)
  Next
End Sub

If you want the full folder name, change the 0 to 1 in this line:

 c.Offset(, 1).Value = Split(dic(c.Text), "|")(0)