Search a directory for a cell value and list folders that contain this value.

[Bagsy]

I understand this is probably a bit of an ask or if it is actually possible, but here goes.
Could somebody please help a bit here, I don’t even know how to start to build this code.

What I would like to achieve is to search in folder W:\1WORKS MANAGERS FILES\WIS FILES DEAD and all subfolders within that folder for a text string in Cell B3. (Which is a drawing number). This can appear as a PDF drawing name, file name or on excel or word documents within the folders being searched)
W:\1WORKS MANAGERS FILES\WIS FILES DEAD has 300+ folders each of these has several subfolders within them.

W:\1WORKS MANAGERS FILES\WIS FILES DEAD

12345 Folder 1
---------Correspondence
---------------Subfolder 1
---------------Subfolder 2
--------------------Sub-Subfolder3 …etc
----------Drawings
---------------Subfolder 1
---------Quotes
---------------Subfolder 1
---------Layout photos
---------Cost sheet
---------Etc

12346 Folder 2
12347 Folder 3

What I am trying to achieve is for the code to search for the drawing number in “B3” then list the parent folders within W:\1WORKS MANAGERS FILES\WIS FILES DEAD
If the code finds the required drawing number in “Subfolder 1” as below
12345 Folder 1
------Correspondence
---------Subfolder 1
Then it should list “12345 Folder 1” on my sheet.

Please note this drawing number will appear in several folders & I would like to list them all.
I need to get these file names on an excel sheet so I can then check if they are listed on another excel document.
This is probably going to be beyond my skill level but would like to give it a try, any help at all would be gratefully received.

 

[Bagsy]

Edit to above
There will always be an Excel file in one of the folders/Subfolders with a cell value which will have the drawing numbers from a Vlookup formula.
Perhaps we could then just search all the folders & subfolders for excel files for the required drawing number and then list the parent folder name

 

[DanteAmor]

Check if the following works for you.

Put all the code in a module and run the Search_directory macro.
Write the data in B3, the result will be in columns C and D.

Option Explicit

Dim rutas As New Collection

Sub Search_directory()
  Dim sPath As String, arch As Variant, sd As Variant, i As Long
  '
  Set rutas = Nothing
  sPath = "W:\1WORKS MANAGERS FILES\WIS FILES DEAD\"
  rutas.Add sPath
  Call AddSubDir(sPath)
  '
  i = 3
  For Each sd In rutas
    If sd Like "*" & Range("B3").Value & "*" Then
      Range("C" & i).Value = sd
      i = i + 1
    End If
    arch = Dir(sd & "\*.*")
    Do While arch <> ""
      If arch Like "*" & Range("B3").Value & "*" Then
        Range("C" & i).Value = sd
        Range("D" & i).Value = arch
        i = i + 1
      End If
      arch = Dir()
    Loop
  Next
  '
  MsgBox "End"
End Sub
'
Sub AddSubDir(lpath)
  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
    DirFile = Dir
  Loop
  For Each sd In SubDir
    rutas.Add sd
    Call AddSubDir(sd)
  Next
End Sub

 

[Bagsy]

Thanks for your help DanteAmor
I Have set up a test folder with folders & sub folders in it and have put an Excel document in one of the folders with the value I have in B3, but when I run the code nothing happens, it does not find this file & does not list the parent folder
It just comes up with the msgbox End

Option Explicit

Dim rutas As New Collection

Sub Search_directory()
  Dim sPath As String, arch As Variant, sd As Variant, i As Long
  '
  Set rutas = Nothing
  'sPath = "W:\1WORKS MANAGERS FILES\WIS FILES DEAD\"
  sPath = "W:\Sub-Contract\Test\"
  rutas.Add sPath
  Call AddSubDir(sPath)
  '
  i = 3
  For Each sd In rutas
    If sd Like "*" & Range("B3").Value & "*" Then
      Range("C" & i).Value = sd
      i = i + 1
    End If
    arch = Dir(sd & "\*.*")
    Do While arch <> ""
      If arch Like "*" & Range("B3").Value & "*" Then
        Range("C" & i).Value = sd
        Range("D" & i).Value = arch
        i = i + 1
      End If
      arch = Dir()
    Loop
  Next
  '
  MsgBox "End"
End Sub
Sub AddSubDir(lpath)
  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
    DirFile = Dir
  Loop
  For Each sd In SubDir
    rutas.Add sd
    Call AddSubDir(sd)
  Next
End Sub

 

[DanteAmor]

What did you put in B3? What is the file name?

 

[DanteAmor]

Do you want to search within the file in all its sheets?

 

[Bagsy]

Hi, appreciate you giving me your time
Yes I would like to search within all excel files on all sheets
Its not a file name as such its just a value in a cell, I’m not looking for a file named as cell B3 value I am looking for any instance of cell B3 value on any excel document within the folder structure.
The test value in B3 I am searching for is 713092 E which I know is on an excel sheet because I have set a sheet up and put it into one of the subfolders for the code to find.
For example, If the code finds the required drawing number on any excel sheet in any folder within parent folder “12345 Folder 1” as below

12345 Folder 1 (this is the parent folder)
----Correspondence
----------Subfolder 1
----------Subfolder 2
---------------Subfolder3 …etc
----Drawings
----------Subfolder 1
----Purchase order
----------Subfolder 1
----Layout photos
----Cost sheet
----etc

Then it should list “12345 Folder 1” on my sheet which would be the parent folder
Thanks for your help

 

[DanteAmor]

any excel sheet in any folder within parent folder

I don't really know what the parent folder would be. If you start in c:\Sub-Contract
and you have 2 subfolder:
- Test1 and Test2
and the value is in a file inside the Test2 subfolder, the paret folder is sub-contract. The same would be if the file is in Test1.

The following will put the folder where there is a file where the value is inside the file on some sheet.

You can start with this. Maybe with the folder you can eliminate the level of subfolders that you need.

Sub Search_directory()
  Dim sPath As String, arch As Variant, sd As Variant, i As Long
  Dim sh1 As Worksheet, wb2 As Workbook, sh2 As Worksheet
  Dim f As Range, sValue As Variant
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set sh1 = ActiveSheet
  sValue = sh1.Range("B3")
  If sValue = "" Then
    MsgBox "Enter a value"
    Exit Sub
  End If
  '
  Set rutas = Nothing
  sPath = "C:\Sub-Contract\"
  '
  rutas.Add sPath
  Call AddSubDir(sPath)
  '
  Range("C3:D" & Rows.Count).ClearContents
  i = 3
  For Each sd In rutas
    arch = Dir(sd & "\*.xls*")
    Do While arch <> ""
      Set wb2 = Workbooks.Open(sd & "\" & arch, False, True)
      For Each sh2 In wb2.Sheets
        Set f = sh2.Cells.Find(sValue, , xlValues, xlPart)
        If Not f Is Nothing Then
          sh1.Range("C" & i).Value = sd
          'Range("D" & i).Value = arch
          i = i + 1
          Exit For
        End If
      Next
      wb2.Close False
      arch = Dir()
    Loop
  Next
  '
  MsgBox "End"
End Sub
'
Sub AddSubDir(lpath)
  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
    DirFile = Dir
  Loop
  For Each sd In SubDir
    rutas.Add sd
    Call AddSubDir(sd)
  Next
End Sub

 

[Bagsy]

Thanks, DanteAmor
What you did for the first of code was what I wanted; except I would like to search within all excel files on all sheets.

sPath = "W:\1WORKS MANAGERS FILES\WIS FILES DEAD\"
In this folder are a number of other folders complete with sub-folders

For example it opens up the first folder in "W:\1WORKS MANAGERS FILES\WIS FILES DEAD\"
Say this folder is called 12345
It opens 12345 and carries out the search within the folder and all subfolders in 12345 on all files in all its sheets
If it finds what I am searching for
It would list folder 12345 on my sheet.
Then goes to the next folder in "W:\1WORKS MANAGERS FILES\WIS FILES DEAD\"
And repeats the process until all folders have been checked.

Apologies if I did not make myself clear
I really appreciate your help
Many thanks
Gary

 

[DanteAmor]

except I would like to search within all excel files

That does the macro of post # 8. Open all excel files, search all sheets and put directory in column C.
Did you try the macro from post # 8?