Looping thru Different folders | VBA & Macros | Excel Forum

Hie Phil,

So basically I have a folder that has 20 subfolders. In those subfolders are excel files that contain customer information. What I am trying to do is locate a file within those folders that contains SS#, lets just say the column name is 'Social Security Number'. Does this make sense?

So far I have gotten as far as looping through the folders and listing the files in each of them.

Thanks for your help

Below is the code.

Sub GetExcelFiles()

    Dim FileSystem As Object
    Dim HostFolder As String
    Dim row As Integer
   
    row = 1
    HostFolder = "Main Folder Location"
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder), row

End Sub
Sub DoFolder(folder, row)
    Dim subfolder As Object
    Dim fileExt As String
    For Each subfolder In folder.SubFolders
        Cells(row, 1).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        subfolder.path, TextToDisplay:= _
        subfolder.path
        row = row + 1
        DoFolder subfolder, row
    Next
    Dim file
    For Each file In folder.Files
        fileExt = Mid(file.Name, InStr(file.Name, ".") + 1, 100)
        If fileExt = "xls" Then
            Cells(row, 2).Select
             ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
             file.path, TextToDisplay:= _
             file.Name
            row = row + 1
        End If
    Next
End Sub

Phil

Yes, I want to look inside each file for a column named 'SSN'.

One sheet per file.

I attached an example of what the files look like.