Search through text files without opening them?

[jpsartrean]

I've got many thousands of text files that I need to search through for a certain substring - "Information Table Value" - and when found output it and - say - the next 15 characters.

Someone on the board already helped me by providing a macro, only trouble is that it requires the files be opened in Excel as worksheets (and the maximum number of these text files that I can open as worksheets appears to be ~800 or so with my POS computer).

Any way to have Excel search without opening each file in a worksheet? The text files are all in a particular folder - I've also included a macro that opens all of them as worksheets in Excel... Perhaps it could open the text files one at a time, search through it, output the string if found, close it, and move onto the next file??

Or then again, perhaps Excel isn't the best way to go about tackling this project.... Any other ideas/programs that may be better suited??

Many thanks in advance...


Open All Files From A Folder As Worksheets Sub

Sub OpenFiles()
 
     'First off, this will prompt where the text files are  saved
    filepath = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "Where are your text files saved")
 
     'this will strip the filename from your selection, leaving just the folder
    Do While Right(filepath, 1) <> "\"
        filepath = Left(filepath, Len(filepath) - 1)
     Loop
 
     'This will   search for all of the files within the folder
    Set fs = Application.FileSearch
    With fs
        .NewSearch
        .FileType = msoFileTypeAllFiles
        .LookIn = filepath
        If .Execute > 0 Then
 
             'For every file that it finds...
            For i = 1 To .FoundFiles.Count
 
                 '...If it ends in txt (if it is a text file!)...
                If Right(.FoundFiles(i), 3) = "txt" Then
                     '...open it...
                    Workbooks.Open (.FoundFiles(i))
                     'and move it into this workbook!!!
                    ActiveSheet.Move After:=ThisWorkbook.Sheets(1)
                End If
 
            Next i
        Else
             MsgBox "There were no files found."
        End If
    End With
 
End Sub

Search For String And Output If Found sub

Sub FindStringManyTimes()
    Dim ii As Integer
    Dim gFind, gPlug As Long
    Dim bRet As Boolean
    Dim oo As Object
 
    Sheets.Add
    ActiveSheet.Name = "ListOfFinds"
    For ii = 2 To Sheets.Count
        With Sheets(ii)
            bRet = bAnyCells(Sheets(ii).Name)
            If bRet Then
                For Each oo In .Cells.SpecialCells(xlCellTypeConstants)
                    gFind = InStr(1, oo.Value, "Information Table Value")
                    If gFind > 0 Then
                        gPlug = gPlug + 1
 
    Cells(gPlug, 1).Value = oo.Value
    Cells(gPlug, 2).Value = Sheets(ii).Name
    Cells(gPlug, 3).Value = oo.Address(False, False)
    Cells(gPlug, 4).Value = oo.Offset(0, 1).Value
    Cells(gPlug, 5).Value = oo.Offset(0, 2).Value
    Cells(gPlug, 6).Value = oo.Offset(0, 3).Value
    Cells(gPlug, 7).Value = oo.Offset(0, 4).Value
                    End If
                Next oo
            End If
        End With
    Next ii
End Sub
Function bAnyCells(sSheet As String) As Boolean
    Dim oo As Object
    On Error GoTo Bye
    With Sheets(sSheet)
        For Each oo In .Cells.SpecialCells(xlCellTypeConstants)
        Next oo
    End With
    bAnyCells = True
Bye:
    On Error GoTo 0
End Function

[Norie]

If the trouble is with the number of files being opened why not open, search, clode and then move onto the next file.

[jpsartrean]

Ummm... I'm hoping someone can help modify my code (or provide another) that would do just that...

I've got more than 50,000 text files. I'm not going to open them each manually and do the search/output... Thought I could open them all at once in Excel and then use it to do the work for me - but the problem is, on my computer Excel can only open ~800 or so of them as worksheets at a time....

[Norie]

Well closing a workbook is pretty straightforward if that's what you want to do.

First of all rather than just opening the workbook, open it and set a reference to it.

Set wbOpen = Workbooks.Open(.FoundFiles(i))

Now you can run your code and then use the reference close the workbook once done with it.

wbOpen.Close