Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching through the contents of a Folder of text files
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching through the contents of a Folder of text files
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling pdf files from general folder to specific folder | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
Exporting folder contents to excel or text format | Excel Discussion (Misc queries) | |||
delete all the contents (sub folders and files) in the temp folder | Excel Discussion (Misc queries) | |||
Searching multiple files and copying text | Excel Programming |