Find given string inside .txt file in folder and copy file to another directory

[JrExceler]

Hello,

I've tried hard to find a solution for my problem but all the codes I've tried don't work or something is missing.

What I need is a Macro that finds a given string inside a .txt file in a given directory (more than 100 files in it) and then copy the file to a specific path.

The code I have is:

Sub StringExistsInFile()
    Dim theString As String
    Dim path As String
    Dim StrFile, NewFile As String
    Dim fso As New FileSystemObject
    Dim file As TextStream
    Dim line As String
     
     
    theString = Range("K3").Value
    path = Range("K4").Value
    StrFile = Dir(path & "*.txt")
     
     
    Do While StrFile <> ""
         
         
        Set file = fso.OpenTextFile(path & StrFile)
        Do While Not file.AtEndOfLine
            line = file.ReadLine
            If InStr(1, line, theString, vbTextCompare) > 0 Then
                 
                FileCopy path & StrFile, "C:\MyData\" & StrFile
                 
                Exit Do
            Else
                 
            End If
        Loop
         
         
        file.Close
        Set file = Nothing
        Set fso = Nothing
         
         
        StrFile = Dir()
    Loop
End Sub

I cannot make it work, do you think you can help me with this?

Thank you!

 

[wideboydixon]

Hi,

I ran your code and it worked OK. I did make one change; "AtEndOfLine" should probably be "AtEndOfStream" - perhaps that would make a difference? You should also remove the "Set fso = Nothing" line since you're trying to dispose of an object that's still in use. What's happening for you? Do you get an error or does it just not work? Have tried setting breakpoints to determine what's happening?

WBD

 

[gallen]

WBD is correct. Change to AtEndOfStream.

I managed to recreate an issue where it only read the first line on a tab delimited file. Changing this line fixed it.

 

[daverunt]

Works for me.
You haven't said what error you are seeing.

Make sure K4 path ends in a backslash or add that as below.
Another possibility is permissions for the paths you are accessing.

Set file = fso.OpenTextFile(path & "\" & StrFile)

 

[JrExceler]

Maybe I have a problem with the path,
because I'm using a network location since the folder is not in my computer:

\\networklocation\Folder1\Folder_2\Folder3\

 

[JrExceler]

The error I get is

Compile Error:
User defined type not defined


These two below are marked with color:
Sub StringExistsInFile()
Dim fso As New FileSystemObject

 

[wideboydixon]

Haha!! I thought you'd already added the reference. I guess you copied/pasted this from somewhere. Go to Tools/References and tick the box next to "Microsoft Scripting Runtime".

WBD

 

[JrExceler]

Yes I found the code on the internet,
Thank you for your help.
I ran the code with the modifications, it worked perfectly however it takes a lot of time and I cannot use Excel during the process but I think I will save me some time at the end.

=)