Searching through the contents of a Folder of text files

[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 =3D CreateObject("Scripting.FileSystemObject")
selectFiles "c:\MyTest"
Set oFSO =3D Nothing

End Sub

'--------------------------------------------------------------------------=
=AD-

Sub selectFiles(sPath)
'--------------------------------------------------------------------------=
=AD-

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

Set Folder =3D oFSO.GetFolder(sPath)

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

For Each file In Folder.Files
If file.Type =3D "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=3Dfile.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

[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.
--
Regards,
John


"jase" wrote:

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