Searching through the contents of a Folder of text files

  • Thread starter Thread starter jase
  • Start date Start date
J

jase

Morning,

Not sure if any one can help on this one! not even too sure if it is
possible in VBA!
I need to search through the contents of a folder full of text files,
and look for a specific word in each file and if the word is found then
various data needs to be extracted from that file.

I was thinking of using the filesystem object for looping through the
files inn the folder i.e the following code that I found will cover
this bit

Dim aryFiles
Dim oFSO

Sub LoopFolders()
Dim i As Integer

Set oFSO = CreateObject("Scripting.FileSystemObject")
selectFiles "c:\MyTest"
Set oFSO = Nothing

End Sub

'--------------------------------------------------------------------------­-

Sub selectFiles(sPath)
'--------------------------------------------------------------------------­-

Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set Folder = oFSO.GetFolder(sPath)

For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr

For Each file In Folder.Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
'>>>>>>>>> your macro here
Activeworkbook.Save
Activeworkbook.Close
End If
Next file

End Sub

HOW DO I SEARCH TEXT FILES FOR A SPECIFIC WORD?....AND THEN EXTRACT
SPECIFIC INFORMATION (ONLY THING IN MY FAVOUR FOR THE EXTRACTION IS
THAT ALL THE TEXT FILES ARE THE SAME LAYOUT)

Any help greatly appreciated

Regards
Jason
 
Read the UsedRange of the sheet into a variant array then process this array
looking for your specific word.

Dim addr as String
Dim vaData() as Variant
addr = ActiveSheet.UsedRange.Address
vaData = Range(addr).value
For x = 0 to UBound(vaData)
For y = 0 to UBound(vadata, 2)
If vaData(x, y) = "your string" then go sub FoundItRoutine
Next y
Next x
Set vaData = nothing

If you know a specific row/col or range of row/col that will have the
search-for-text. then you can limit the for loops. Just remember that the
array cells start at 0. so a 10x10 range on your sheet will be in (0..9,
0..9)

Hope that helps.
 
Back
Top