Searching for content in a large number of Excel files

Question

I have 2500 Excel files. I need to output all rows which contain a certain string in a certain column. How can I do this? What if the certain string is not in a fixed column, but could be in any column?

Answer 1

Here's some skelet-O code. You can either build on this or someone else here can. Large chunks are yet to be written. Maybe I'll do more when I get home.

Option Explicit

Sub findInFolders()
    Dim folderName As String 'this is where all the files reside, some extra work is neede if there are sub directories
    'folderName = <put your folder name here>
    Dim files() As String: Set files = GetFolderContents
    Dim i As Integer

    Dim wb As Workbook, sht As Worksheet
    For i = LBound(files) To UBound(files)

        Set wb = Application.Workbooks.Open(files(i))
        For Each sht In wb.Sheets
            GetRowsBasedOnString searchString, sht
        Next sht

        wb.Close False
        Set wb = Nothing
    Next i
End Sub


Function GetFolderContents(folderName As String) As String()
    Dim fso As FileSystemObject: Set fso = New FileSystemObject
    GetFolderContents = fso.GetFolder(folderName).files
End Function

Function GetRowsBasedOnString(searchString As String, sht As Worksheet)
    'loop through range or use find or whatever. Find the value your looking for
    Dim found As Boolean, rng As Range
    If found Then ReportFoundRow rng
End Function

Function ReportFoundRow(foundRow As Range)
    'write your found data to your master workbook
End Function

Answer 2

From your comments I assume you have never written a VBA macro. Your first macro will be an unhill climb but after that each one will be easier until you forget you ever thought they might be difficult to write.

The macro below assumes that all 2,500 workbooks are in the same folder. This is usually the easiest approach but it may not be possible in your case. If it is not possible, pick a folder with lots of workbooks to try out this macro. You will have to add an explanation of your situation to your question so I can explain how this macro can be adapted to address it.

I have tried to keep things simple although it may not seem that way. There are better, faster ways of doing the same thing but I think this is the right compromise. I have included lots of comments explaining what the code does. The macro editor's Help will explain the syntax. But do ask if you are struggling.

Create a new workbook in the folder you have picked for the test. My code expects a worksheet named "Bobert" which is convenient for me. Pick a name that makes sense to you and change the code to match; I tell you how later.

Select Tools then Macro then Visual Basic Editor or click Alt+F11.

Down the left you will have the Project explorer. At the top on the right you will have a grey area. At the bottom on the right you will have the immediate window. I may talk about the immediate window later.

Select Insert then Module. "Module1" will be added to the project explorer and the grey area goes white. This is the code area for Module1.

You can leave the module name as "Module1" or you can change it. Click F4. The Properties window will display. The only property for a module is its name. Click "Module 1" in "(Name) Module1", backspace out "Module1" and type in a name of your choice. Close the Properties window.

Copy the code below to the code area.

This macro tackles the first part of your problem: it finds all the worksbooks in the folder and all the worksheets within those workbooks. It creates a list of those worksheets in worksheet "Bobert". If the 2,500 workbooks cannot be brought together into a single folder you may need a macro like this to build a list of the workbooks and worksheets to be examined but this macro is intended as a training exercise. Create a header line:

 A1 = Folder
 B1 = Workbook
 C1 = Worksheet

The only statement you will need to change immediately is:

  Set WShtDest = ActiveWorkbook.Worksheets("Bobert")

Change "Bobert" to the name you have chosen for the worksheet in which the list of worksheets will be created.

Place the cursor on the statement:

    RowDestCrnt = .Cells(Rows.Count, "A").End(xlUp).Row + 1

and click F9. The line will go brown because you have made it a breakpoint which I explain in a moment.

Each time you click F8, one statement of the code will be obeyed. This allows you step through the code. If you place the cursor over a variable name, its value will be displayed. You can switch to the worksheet to check what has changed.

If you think you understand a block of code, click F5 and the code will run until the next breakpoint. I have set one but you can set as many as you want.

I hope this gives you something to think about. Answer my questions and I can give you the next bit of the solution.

Option Explicit
' Searching for content in a large number of Excel files
' http://superuser.com/q/452980/108084
Sub ListWorksheets()

  Dim ColDestCrnt As Long
  Dim FileNameList() As String
  Dim InxFNL As Long
  Dim InxW As Long
  Dim PathCrnt As String
  Dim RowDestCrnt As Long
  Dim WBkSource As Workbook
  Dim WShtDest As Worksheet

  Application.ScreenUpdating = False

  ' Create a reference to the worksheet in which data will be stored
  ' Change "Bobert" to your name for the destination worksheet.
  Set WShtDest = ActiveWorkbook.Worksheets("Bobert")

  ' This assumes the source workbooks are in the same folder as the workbook
  ' holding this macro.  You could replace this with a statement like:
  '   PathCrnt = "C:\MyFiles"
  PathCrnt = ActiveWorkbook.Path

  ' GetFileNameList is a subroutine I wrote sometime ago.  It returns an
  ' array of filenames within a specified folder (PathCrnt) that match a
  ' specified format (*.xls).
  Call GetFileNameList(PathCrnt, "*.xls", FileNameList)

  ' Get the next free row in worksheet Bobert.  By calling this routine with
  ' different values for PathCrnt, you could built up a list containing files
  ' from many folders.
  With WShtDest
    RowDestCrnt = .Cells(Rows.Count, "A").End(xlUp).Row + 1
  End With

  For InxFNL = LBound(FileNameList) To UBound(FileNameList)
    If FileNameList(InxFNL) <> ActiveWorkbook.Name Then
      ' In the Workbook Open statement, 0 means "do not update any links" and
      ' True means "open read only"
      Set WBkSource = Workbooks.Open(PathCrnt & "\" & FileNameList(InxFNL), 0, True)
      With WBkSource
        ' Record the name of each worksheet in the workbook
        For InxW = 1 To .Worksheets.Count
          WShtDest.Cells(RowDestCrnt, "A").Value = PathCrnt
          WShtDest.Cells(RowDestCrnt, "B").Value = FileNameList(InxFNL)
          WShtDest.Cells(RowDestCrnt, "C").Value = .Worksheets(InxW).Name
          RowDestCrnt = RowDestCrnt + 1
        Next
        .Close SaveChanges:=False     ' Close this source workbook
      End With
    End If
  Next

End Sub
Sub GetFileNameList(ByVal PathCrnt As String, ByVal FileSpec As String, _
                                            ByRef FileNameList() As String)

' This routine sets FileNameList to the names of files within folder
' PathCrnt that match FileSpec.  It uses function Dir$() to get the file names.
' I can find no documentation that says Dir$() gets file names in alphabetic
' order but I have not seen a different sequence in recent years.

  Dim AttCrnt As Long
  Dim FileNameCrnt As String
  Dim InxFNLCrnt As Long

  ' I initialise the array with space for 100 files and then enlarge it if
  ' necessary.  This is normally enough space for  my applications but since
  ' you are expecting 2,500 files I have replaced the original statement.
  'ReDim FileNameList(1 To 100)
  ReDim FileNameList(1 To 2500)
  InxFNLCrnt = 0

  ' Ensure path name ends in a "\"
  If Right(PathCrnt, 1) <> "\" Then
    PathCrnt = PathCrnt & "\"
  End If

  ' This Dir$ returns the name of the first file in
  ' folder PathCrnt that matches FileSpec.
  FileNameCrnt = Dir$(PathCrnt & FileSpec)
  Do While FileNameCrnt <> ""
    ' "Files" have attributes, for example: normal, to-be-archived, system,
    ' hidden, directory and label. It is unlikely that any directory will
    ' have an extension of XLS but it is not forbidden.  More importantly,
    ' if the files have more than one extension so you have to use "*.*"
    ' instead of *.xls", Dir$ will return the names of directories. Labels
    ' can only appear in route directories and I have not bothered to test
    ' for them
    AttCrnt = GetAttr(PathCrnt & FileNameCrnt)
    If (AttCrnt And vbDirectory) <> 0 Then
      ' This "file" is a directory.  Ignore
    Else
      ' This "file" is a file
      InxFNLCrnt = InxFNLCrnt + 1
      If InxFNLCrnt > UBound(FileNameList) Then
        ' There is a lot of system activity behind "Redim Preserve".  I reduce
        ' the number of Redim Preserves by adding new entries in chunks and
        ' using InxFNLCrnt to identify the next free entry.
        ReDim Preserve FileNameList(1 To 100 + UBound(FileNameList))
      End If
      FileNameList(InxFNLCrnt) = FileNameCrnt
    End If
    ' This Dir$ returns the name of the next file that matches
    ' the criteria specified in the initial call.
    FileNameCrnt = Dir$
  Loop

  ' Discard the unused entries
  ReDim Preserve FileNameList(1 To InxFNLCrnt)

End Sub