How to open text files and move them to a folder using VBA?

Question

I need some help with a project in which I must open a list of text files, find a pattern in their contents and then move to other folders according to the pattern.

For example, in a list of text files, I must find which of them have the word "blue" written inside and them move only those to another folder named "Blue".

I was tring to do it using the command FileSystemObject, but I was kind of lost.

Thanks a lot in advance!!

Please update your question to include the code you have tried so far — Alex S, Sep 10 '15 at 14:58
I agree with Alex about posting what you have so far... also maybe excel & vba might not be the best language for this project (unless you absolutely must) ... off the cuff I would suggest looking into Perl since text manipulation is specifically what it's for. Here is a link specifically about scripts that move text files based on criteria : http://www.perlmonks.org/?node_id=778993 and here is one about Perl : https://www.perl.org/about.html — The Gambill, Sep 11 '15 at 05:14

score 1 · Accepted Answer · answered Sep 11 '15 at 12:01

Dim sDir As String
Dim sPath As String
Dim sPattern as String
Dim sReadedData as String
dim sDestiny as string
dim sPathDestiny as string
Dim fso As Object

Set fso = VBA.CreateObject("Scripting.FileSystemObject")

sPath$ = "c:\YourFolder"
sDir$ = Dir(sPath, vbDirectory)
sPattern= "abcdefg"
sDestiny="c:\DestinyFolder"

If sDir = "" Then
MsgBox "Path " & sDir & " Not Found"
End
End If
sDir$ = Dir(sPath & "\*.txt")
Do Until sDir = ""
     sPathDestiny=Replace(sDir, sPath, sDestiny)
     Open sDir$ For Input As #1
     do until EOF(1)   
         Input #1, sReadedData
     loop
     if InStr(sReadedData, sPattern)>0 then
         Call fso.CopyFile(sDir, sPathDestiny)
     end if
Loop

This is the main idea. Play with it.

How to open text files and move them to a folder using VBA?

1 Answers1