Alistair_Mair
asked on
search all folders, and copy files with a matching text string (regardless of file ext) into a target folder.
I'm looking for a bat file or macro to run through an existing excel or text file listing around 57000 numbers (which consist of both numbers and text). Find the corresponding files which contains an identical text string (but which may have additional characters in the string at the end not limited to the file extension). The search function should search all sub folders within an identified root folder. The identified files should then be copied (not moved) to an identified target folder.
ASKER
Hi Kimputer,
changed the parameters as requested and Copied your script as detailed below into the macro application on excel 2010 and I get the error "compile error, invalid outside procedure" ??
--
Option Explicit
Const ForReading = 1
Const ForWriting = 2
Dim scan_dir, copy_dir, folder, fileCollection, file_read, test_text, textfile, ObjFSO, folderCollection, file, subfolder
scan_dir = "c:\DRAGADOS\"
textfile = "c:\temp\temp.txt"
copy_dir = "c:\temp\P3FILES\"
Set ObjFSO = CreateObject("Scripting.Fi leSystemOb ject")
Set file_read = ObjFSO.OpenTextFile(textfi le, ForReading)
Do Until file_read.AtEndOfStream
test_text = file_read.Readline
searchsub scan_dir, test_text
Loop
Set file_read = Nothing
Sub searchsub(Source, text)
Set folder = ObjFSO.GetFolder(Source)
Set fileCollection = folder.Files
Set folderCollection = folder.Subfolders
For Each file In fileCollection
If InStr(file.Name, text) Then
ObjFSO.CopyFile file, copy_dir & file.Name
End If
Next
For Each subfolder In folderCollection
'wscript.echo subfolder & text
searchsub subfolder, text
Next
End Sub
changed the parameters as requested and Copied your script as detailed below into the macro application on excel 2010 and I get the error "compile error, invalid outside procedure" ??
--
Option Explicit
Const ForReading = 1
Const ForWriting = 2
Dim scan_dir, copy_dir, folder, fileCollection, file_read, test_text, textfile, ObjFSO, folderCollection, file, subfolder
scan_dir = "c:\DRAGADOS\"
textfile = "c:\temp\temp.txt"
copy_dir = "c:\temp\P3FILES\"
Set ObjFSO = CreateObject("Scripting.Fi
Set file_read = ObjFSO.OpenTextFile(textfi
Do Until file_read.AtEndOfStream
test_text = file_read.Readline
searchsub scan_dir, test_text
Loop
Set file_read = Nothing
Sub searchsub(Source, text)
Set folder = ObjFSO.GetFolder(Source)
Set fileCollection = folder.Files
Set folderCollection = folder.Subfolders
For Each file In fileCollection
If InStr(file.Name, text) Then
ObjFSO.CopyFile file, copy_dir & file.Name
End If
Next
For Each subfolder In folderCollection
'wscript.echo subfolder & text
searchsub subfolder, text
Next
End Sub
I said it was VBscript (because it's easier that way, inside Excel it's VBA). Run it at the command prompt. Copy all the code into a text file, then give the script a name, something like "search.vbs"
ASKER
Sorry, got it and ran it. It does seem to be working but very slowly (been running for half and hour and so far copies 8 files). It's looking through 400gb of data for these files, Does this code specify to look for each file individually and cycle through the folders and then look for the next file with a corresponding file name and cycle through again or cycles through once virtually stores the results and then copies across to the target folder?
It could be slow indeed. I was hoping your disk and cache would kick in, but seems it doesn't. I was trying to keep the code clean. Would need a lot of time to code something to load all the files on the disk into memory and do the matching there.
ASKER
OK, it's just that at this rate it will take around 50 days to cycle through and find all the files. If it is unreasonable to expect a more workable solution I will credit you with the points?
If you could give me a day? (Could still fail though, if memory not sufficient)
ASKER
I can certainly wait a day. I will credit you with at least half the points tomorrow one way or the other. If someone finds a more workable solution in the interim then half the points go to them, does that sound fair?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry, I got caught up in other stuff and this slipped my mind. Not tested it but appreciate the work you've put in and have assigned you the points, I apologise for the delay in getting back to you.
Those things need to be adjusted to your own:
scan_dir = "c:\windows\" (folder to be scanned)
textfile = "c:\temp\temp.txt" (here are the parts of the file names)
copy_dir = "c:\temp\" (files will be copied here)
Open in new window