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
Search For String And Output If Found sub
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