Searching through the contents of a Folder of text files

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
 
J

John Keith

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top