+ Reply to Thread
Results 1 to 2 of 2

Loop through each folder and then loop through each file to find file with certain keyword

Hybrid View

  1. #1
    Registered User
    Join Date
    08-25-2010
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    52

    Loop through each folder and then loop through each file to find file with certain keyword

    I was able to figure out a way for the program to loop through the folder, but I am having trouble having it loop through the subfolders in the main directory.
    
    Sub SearchDeviceFile()
    
    Dim fso, fldr, fil, myfolder As Object
    Dim wb As Workbook
    Dim devfile As String
    Dim d As String, ext, x
    Dim srcPath As String, destPath As String, srcFile As String
    
    Set fso = CreateObject("scripting.filesystemobject")
    Set fldr = fso.getfolder(Dir("C:\*", vbDirectory))
    
    srcPath = fldr & "\"
    destPath = "C:\UNM1\"
    ext = Array("*History(Device)*.csv")
    
    
    For Each x In ext
        d = Dir(srcPath & x)
            
            Do While d <> ""
                srcFile = srcPath & d
                FileCopy srcFile, destPath & d
                d = Dir
            Loop
    Next
    
    
    End Sub

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Loop through each folder and then loop through each file to find file with certain key

    maybe you can arrange this code
    Dim FSO As Object, iFolder As Object, iFile As Object, FD As FileDialog, ExtArray() As Variant
    Dim iPath As String, firstAddress As String, iPathName As String, Recursion As Boolean
    Dim iSht As Worksheet, ws As Worksheet, iTempWB As Workbook, ExcelVersion As Byte
    Dim TextToFind, iFoundRng As Range, iLastRow As Long, FoundAny As Boolean, iTotalFiles As Long
    
    Sub SearchInAllFilesAndFolders()
    Set ws = ThisWorkbook.ActiveSheet
    Recursion = False: iPathName = "": FoundAny = False
        TextToFind = Application.InputBox("Enter the text for search:", "Search")
        If TextToFind = "" Or TextToFind = False Then Exit Sub
        TextToFind = Trim(TextToFind)
        Set FD = Application.FileDialog(msoFileDialogFolderPicker)
        With FD
            .AllowMultiSelect = False
            .Title = "Specify the necessary directory"
            .ButtonName = "OK"
            If .Show = False Then Exit Sub Else iPath = .SelectedItems(1) & Application.PathSeparator
        End With
        Set FD = Nothing
        If MsgBox("To look through the enclosed folders?", vbQuestion + vbYesNo, "Recursion") = vbYes Then Recursion = True
    ExtArray = Array("xls", "xlsx", "xlsm", "xlsb") 'Here you can specify which extension will process
    Set FSO = CreateObject("Scripting.FileSystemObject")
    ChooseFoldersSubfoldersFSO (iPath)
    Set iFolder = Nothing
    Set FSO = Nothing
    ws.Cells(2, 1).Select
    If FoundAny = False Then
       MsgBox "Text none of the files in the folder:" & Chr(10) & iPath & Chr(10) & " was not found!", 48, "Report"
       Exit Sub
    End If
    MsgBox "Search completed!" & Chr(10) & "Total processed: " & iTotalFiles & " files", 64, "Search"
    End Sub
    
    Function ChooseFoldersSubfoldersFSO(ByVal sPath As String)
    Set iFolder = FSO.GetFolder(sPath)
    For Each iFile In iFolder.Files
      If Not IsError(Application.Match(FSO.GetExtensionName(iFile), ExtArray(), 0)) Then
        If iFile.Name <> ThisWorkbook.Name Then
          Set iTempWB = Workbooks.Open(Filename:=sPath & iFile.Name, UpdateLinks:=False, ReadOnly:=True)
          iTotalFiles = iTotalFiles + 1
          For Each iSht In iTempWB.Worksheets
            If iSht.FilterMode = True Then iSht.ShowAllData
               Set iFoundRng = iSht.Cells.Find(What:=TextToFind, LookIn:=xlFormulas, LookAt:=xlWhole)
               If Not iFoundRng Is Nothing Then
                  FoundAny = True
                  firstAddress = iFoundRng.Address
                  With ws
                    Do
                      iLastRow = .UsedRange.Rows.Count + .UsedRange.Row
                      If iPathName <> sPath Then  'If a new file
                        iPathName = sPath
                        With .Cells(iLastRow + 2, 1)
                          .Value = "Directory: " & sPath
                          .Font.Bold = True
                          '.Font.ColorIndex = 5
                        End With
                        .Hyperlinks.Add Anchor:=.Cells(iLastRow + 3, 1), Address:=sPath & iTempWB.Name, ScreenTip:="Book: " & iTempWB.Name & ", Sheet: " & iSht.Name, TextToDisplay:="Book: " & iTempWB.Name & ", Sheet: " & iSht.Name
                      Else
                        .Hyperlinks.Add Anchor:=.Cells(iLastRow + 1, 1), Address:=sPath & iTempWB.Name, ScreenTip:="Book: " & iTempWB.Name & ", Sheet: " & iSht.Name, TextToDisplay:="Book: " & iTempWB.Name & ", Sheet: " & iSht.Name
                      End If
                      iFoundRng.EntireRow.Copy   'copy the entire row
                      .Cells(.UsedRange.Rows.Count + .UsedRange.Row, "A").PasteSpecial xlPasteValues    'paste values ??only
                      Set iFoundRng = iSht.Cells.FindNext(iFoundRng)
                    Loop While iFoundRng.Address <> firstAddress
                  End With
                End If
            Next
            Application.CutCopyMode = False
            iTempWB.Close SaveChanges:=False
        End If
      End If
    Next
    If Recursion Then    'recursion
       For Each iFolder In iFolder.SubFolders
          ChooseFoldersSubfoldersFSO iFolder.Path & Application.PathSeparator
       Next
    End If
    End Function
    If solved remember to mark Thread as solved

+ 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. VBA Loop for a folder of all the files, Loop all the worksheet in each workbook
    By nanjingwoodworking in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-07-2013, 07:20 PM
  2. Replies: 4
    Last Post: 09-09-2013, 05:06 AM
  3. [SOLVED] Loop Search for PDF file in folder
    By ANDREWA in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-01-2012, 02:18 PM
  4. Save File As Cell Value (loop through folder)
    By uncleslinky in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-22-2012, 04:02 AM
  5. Find File in Directory(s) and Open Folder highlighting file
    By cpadude in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-08-2010, 05:39 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