Results 1 to 4 of 4

Search through text files without opening them?

This is a discussion on Search through text files without opening them? within the Excel Questions forums, part of the Question Forums category; I've got many thousands of text files that I need to search through for a certain substring - "Information Table ...

  1. #1
    New Member
    Join Date
    Feb 2008
    Posts
    44

    Default Search through text files without opening them?

    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

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,269

    Default Re: Search through text files without opening them?

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

  3. #3
    New Member
    Join Date
    Feb 2008
    Posts
    44

    Default Re: Search through text files without opening them?

    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....

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,269

    Default Re: Search through text files without opening them?

    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
    If posting code please use code tags.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com