Search through text files without opening them?

jpsartrean

New Member
Joined
Feb 19, 2008
Messages
44
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

Code:
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

Code:
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
If the trouble is with the number of files being opened why not open, search, clode and then move onto the next file.
 
Upvote 0
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....
 
Upvote 0
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.
Code:
Set wbOpen = Workbooks.Open(.FoundFiles(i))
Now you can run your code and then use the reference close the workbook once done with it.
Code:
wbOpen.Close
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top