+ Reply to Thread
Results 1 to 2 of 2

Search Text Excel All files & Subfolders

Hybrid View

  1. #1
    Forum Contributor Jack7774's Avatar
    Join Date
    08-31-2012
    Location
    Georgia, US
    MS-Off Ver
    Excel 2010
    Posts
    576

    Post Search Text Excel All files & Subfolders

    I would like run a vba script that will search for a text lets say "ACDC" under the following path "C:\Music" including all subfolders for excel files ending .xls or .xlsm that contain a text and if found place full directory name and file name on new workbook. I've found this by Allen Wyatt but it doesn't search including subfolders. It also doesn't search both .xls and .xlsm at the same time but thats not a big deal unless someone feels like adding that in too.

    Can someone either assist in modifying the code below to including searching including subfolders or write a new code? Much thanks.

    Sub SearchFolders()
        Dim fso As Object
        Dim fld As Object
        Dim strSearch As String
        Dim strPath As String
        Dim strFile As String
        Dim wOut As Worksheet
        Dim wbk As Workbook
        Dim wks As Worksheet
        Dim lRow As Long
        Dim rFound As Range
        Dim strFirstAddress As String
    
        On Error GoTo ErrHandler
        Application.ScreenUpdating = False
    
        'Change as desired
        strPath = "C:\Music"
        strSearch = "ACDC"
    
        Set wOut = Worksheets.Add
        lRow = 1
        With wOut
            .Cells(lRow, 1) = "Workbook"
            .Cells(lRow, 2) = "Worksheet"
            .Cells(lRow, 3) = "Cell"
            .Cells(lRow, 4) = "Text in Cell"
            Set fso = CreateObject("Scripting.FileSystemObject")
            Set fld = fso.GetFolder(strPath)
    
            strFile = Dir(strPath & "\*.xls*")
            Do While strFile <> ""
                Set wbk = Workbooks.Open _
                  (Filename:=strPath & "\" & strFile, _
                  UpdateLinks:=0, _
                  ReadOnly:=True, _
                  AddToMRU:=False)
    
                For Each wks In wbk.Worksheets
                    Set rFound = wks.UsedRange.Find(strSearch)
                    If Not rFound Is Nothing Then
                        strFirstAddress = rFound.Address
                    End If
                    Do
                        If rFound Is Nothing Then
                            Exit Do
                        Else
                            lRow = lRow + 1
                            .Cells(lRow, 1) = wbk.Name
                            .Cells(lRow, 2) = wks.Name
                            .Cells(lRow, 3) = rFound.Address
                            .Cells(lRow, 4) = rFound.Value
                        End If
                        Set rFound = wks.Cells.FindNext(After:=rFound)
                    Loop While strFirstAddress <> rFound.Address
                Next
    
                wbk.Close (False)
                strFile = Dir
            Loop
            .Columns("A:D").EntireColumn.AutoFit
        End With
        MsgBox "Done"
    
    ExitHandler:
        Set wOut = Nothing
        Set wks = Nothing
        Set wbk = Nothing
        Set fld = Nothing
        Set fso = Nothing
        Application.ScreenUpdating = True
        Exit Sub
    
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Sub
    Thank those who have helped you by clicking the Star * below their name and please mark your post [SOLVED] if it has been answered satisfactorily.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,080

    Re: Search Text Excel All files & Subfolders

    This requires Microsoft Scripting Runtime to be set. In the VBA Editor, click Tools...References....Microsoft Scripting Runtime. Run the ListFileNames macro. The result will be returned to "Sheet1" of the workbook with the macro.
    Sub ListFileNames()
        Const sRoot     As String = "C:\Test\"
        Dim oFSO        As Scripting.FileSystemObject
        Application.ScreenUpdating = False
        Set oFSO = New Scripting.FileSystemObject
        RecurseFolder oFSO, sRoot, True
    End Sub
     
    Sub RecurseFolder(oFSO As FileSystemObject, sDir As String, IncludeSubFolders As Boolean)
        Dim oFil        As File
        Dim oFld        As Folder
        Dim oSub        As Folder
        Dim Val As Range, desSH As Worksheet
        Set desSH = ThisWorkbook.Sheets("Sheet1")
        desSH.Range("A1:B1") = Array("Path", "Sheet Name")
        Set oFld = oFSO.GetFolder(sDir)
        For Each oFil In oFld.Files
            If oFil.Name Like "*.xls" Or oFil.Name Like "*.xlsm" Then
                Set wb = Workbooks.Open(Filename:=oFil)
                For Each ws In Sheets
                    Set Val = ws.UsedRange.Find("ACDC", LookIn:=xlValues, lookat:=xlWhole)
                    If Not Val Is Nothing Then
                        With desSH
                            .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = wb.Path
                            .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = wb.Name
                        End With
                        Exit For
                    End If
                Next ws
                wb.Close False
            End If
        Next oFil
        If IncludeSubFolders Then
            For Each oSub In oFld.SubFolders
                RecurseFolder oFSO, oSub.Path, True
            Next oSub
        End If
    End Sub
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] list of subfolders in folder - without files and sub-subfolders
    By MartyZ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-11-2022, 10:56 AM
  2. [SOLVED] Search for files in folders and subfolders
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-16-2014, 08:43 AM
  3. VBA directory search for files stored on mac & in subfolders
    By thebute in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-20-2014, 04:02 AM
  4. [SOLVED] Files within Multiple SubFolders and SubFolders Within It
    By codeslizer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-18-2013, 04:18 AM
  5. [SOLVED] Search subfolders in excel 2010
    By greasybob in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-08-2012, 07:03 PM
  6. replace text in files within subfolders
    By pieros in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-01-2005, 10:05 AM
  7. [SOLVED] copy subfolders, replace text in files and save files in copied subfolders
    By pieros in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-01-2005, 09:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1