• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to search Text in all xls* files & sub folder in a directory

S

ssuresh98

Member
Hello XL Gurus,
I have a list of IDs in a file and I need a macro to loop through the xls* files in a directory (which has ~100 folders, each folder has ~10 sub folders). Literally there are 1000s of files and each ID is in a folder. To make it faster, I could have the IDs and the Folder Name in a table. I found the following macro here in the forums but need to tweak a little to search sub folders. Can someone help me with this?
Thanks in advance for any help.

Code: 
Sub LoopThroughFiles()
    Dim file As Variant
    Dim sht As Worksheet, path As String, y As Range
   
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
   
    i = 2
    path = ThisWorkbook.Sheets("sheet1").Range("a2").Value
    If Right(path, 1) <> "\" Then path = path & "\"
   file = Dir(path & "*.xls*")
   While (file <> "")
   Workbooks.Open path & file
   For Each cell In ThisWorkbook.Sheets("sheet1").Range("list")
   
   For Each sht In ActiveWorkbook.Sheets
   
    Set y = sht.Cells.Find(What:=cell.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
   
    If y Is Nothing Then
    GoTo Skip:
    Else
    ThisWorkbook.Sheets("sheet2").Range("a" & i).Value = cell.Value
    ThisWorkbook.Sheets("sheet2").Range("b" & i).Value = y.Address
    ThisWorkbook.Sheets("sheet2").Range("c" & i).Value = sht.Name
    ThisWorkbook.Sheets("sheet2").Range("d" & i).Value = file
    ThisWorkbook.Sheets("sheet2").Range("e" & i).Value = path
    i = i + 1
    End If
    found = y.Address
   
Skip:
Next sht
Next cell
ActiveWorkbook.Close
     
     file = Dir()
  Wend
On Error GoTo 0
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
 
Hi Chihiro, thanks for the link. It does the same as the code that I have posted. The macro looks into files in a folder but it is unable to check sub folders.
Thank you, hope you can help modify so that it can check sub folders.
 
Hi Chihiro,
Thanks for the link. I tried to play with the code and came up with the following that works but with a few glitches.
The combined macro works for a small folder but in my case I have a complicated file system (1 major folder with ~1000 folders containing 10-20 sub-folders, ~10-20 files each).

The macro stalls whenever a file has links to be updated or requires input from the user. As you can see, I have the display alerts turned off but it does not work. For some reason it keeps asking for user input. Also for some strange reason it stops when it reaches a particular file. I see no reason for the macro to error out. Otherwise is works for a small file structure.



Code: 
Public Sub NonRecursiveMethod()
    Dim FSO, oFolder, oSubfolder, oFile, queue As Collection
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
   
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set queue = New Collection
    queue.Add FSO.GetFolder("\\myfiles\ABC\SR\SR Lab\Studies") 

    Do While queue.Count > 0
        Set oFolder = queue(1)
        queue.Remove 1 'dequeue
             
        For Each oSubfolder In oFolder.SubFolders
            queue.Add oSubfolder 'enqueue
            Sheets("Sheet1").Range("A2") = oSubfolder.path
            
Call LoopThroughFiles
                   
                Next oSubfolder
           
    Loop
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
   
   
MsgBox "Done"

End Sub


Sub LoopThroughFiles()
    Dim file As Variant
    Dim sht As Worksheet, path As String, y As Range
   
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
   
    i = 2
    path = ThisWorkbook.Sheets("sheet1").Range("a2").Value
    If Right(path, 1) <> "\" Then path = path & "\"
   file = Dir(path & "*.xls*")
   While (file <> "")
   Workbooks.Open path & file
   For Each cell In ThisWorkbook.Sheets("sheet1").Range("list")
   
   For Each sht In ActiveWorkbook.Sheets
   
    Set y = sht.Cells.Find(What:=cell.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
   
    If y Is Nothing Then
    GoTo Skip:
    Else
    ThisWorkbook.Sheets("sheet2").Range("a" & i).Value = cell.Value
    ThisWorkbook.Sheets("sheet2").Range("b" & i).Value = y.Address
    ThisWorkbook.Sheets("sheet2").Range("c" & i).Value = sht.Name
    ThisWorkbook.Sheets("sheet2").Range("d" & i).Value = file
    ThisWorkbook.Sheets("sheet2").Range("e" & i).Value = path
    i = i + 1
    End If
    found = y.Address
   
Skip:
Next sht
Next cell
ActiveWorkbook.Close
     
     file = Dir()
  Wend
On Error GoTo 0
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
 
One thing, when you are declaring variables in one dim statement.

The way you coded, everything except "queue" is declared as variant. Which will slow down your code.

See link for best practices.
http://www.cpearson.com/excel/declaringvariables.aspx

As I originally stated, using macro to search through large collection of files is very inefficient and will have issues. I'd recommend at least using Windows Search and/or grep to narrow down the files that you need to open.
 
Thanks for your reply Chihiro.

I have started looking into grep as well. Since I am not an expert, can i ask you if it is possible to do the same thing using grep?

I have a list of strings (~700) that I need to search in files within a folder (with subfolders). Can we use loop in grep to do this? I tried 1 string and it took forever in grep as well (~18000 xls* files), but time is not a factor I am worried about, we just want to be able to automate it.

Thanks for taking the time to help.
 
Grep command line accepts regular expression (REGEX) you can string together multiple search criteria.

Something like below in it's simplest form
Code: 
grep 'word1|word2|word3' /path/to/file

Searches for word1, 2 or 3 in a given file. You can string together multiple searches and also have grep create/append to txt file.

I often work with command line batch files and use scheduler to fire it.

But I'm getting off topic. For Grep related questions, best ask in the forum related to the software of your choice.
 
The grep solution for my problem is welcome but it no faster than the vba macro solution. Also since I am so close to the vba macro solution and this being a excel related forum I would still like to pursue my issue here.

One issue that I have is that the display alerts is not turning off. This could be a simple rookie mistake. Can someone take a look at the code and let me know how to fix it?
Thanks
 
For Data Connection add this line along with Display Alerts.
Code: 
Application.AskToUpdateLinks = False

As for user input, what exactly is requiring user input? Macro on the sheet (being opened) or something else?
 
You must log in or register to reply here.
Back
Top