1

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?

9
  • Unfortunately, i'm 99% sure that this is not possible without programming. There is certainly no featureset in Excel that can execute such a function. I'm not aware of a third party application that can do this either. The only thing i can think of is to merge all the excel files into one enormous file and extract the rows from there, although that seems impractical given the amount of files you're talking about.
    – Oliver G
    Jul 24, 2012 at 15:44
  • Windows Search indexes the content of Office files. It comes with Windows 7, but you can install it for XP.
    – James
    Jul 24, 2012 at 16:10
  • @James: Won't that only return files that happen to contain the text I'm looking for, but not the rows that contain that text?
    – Bobert
    Jul 24, 2012 at 18:39
  • 1
    I agree with Oliver, you will not solve this without programming. Why is that important? A little macro to examine every Excel workbook in a folder would not be difficult to write although not necessarily quick to run. Jul 25, 2012 at 10:26
  • 1
    Question 1 Can the Excel workbooks be gathered into a single folder or a single tree? Question 2 You first ask about a certain string in a certain column and then in any column. Do you want both? Question 3 Is this a one-off task or something repeated every day with different strings or something in between? Jul 25, 2012 at 16:42

2 Answers 2

0

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
0

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
3
  • Two thoughts: I'm of the opinion that the Dir function is one of the most confusing functions in VBA. Calling does different things depending on where you call it and previous calls. It's real hard to follow for someone starting out. I lean on the FileSystemObject a lot because it has strong typing support for folder/file objects and there are lots of established methods/parameters at your finder tips.
    – Brad
    Jul 26, 2012 at 13:54
  • Secondly, why create static arrays and Redim them when you have collection objects? They are dynamic, you never have to diagnose errors like forgetting to call Preserve on a Redim and having your array be erased (another strange concept for a beginner), and they're easier to search/loop through.
    – Brad
    Jul 26, 2012 at 13:54
  • @Brad. VBA is difficult because it has so many different ways of achieving the same objective. If you maintain other people's code, you have to know every technique because they are all used by someone. In my experience, beginners find the FileSystemObject functions and collections more difficult to understand than older functionality such as Dir and arrays. I believe this is because it is difficult to find good explanations of FileSystemObject and collections. If you can provide such explanations it will give Bobert the chance to understand and select from different techniques. Jul 26, 2012 at 20:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .