VBA code to Copy File Based on Keywords in File Name

[Parth13]

Hi,
I am trying to write a code which will copy files from one directory to other based on some keywords in File Name.
The keywords are specified in a range in the spreadsheet. The code should pick each keyword, search for files containing that keyword and copy all the files with that keyword in the name to the directory specified. If the keyword is not found, it highlights the cell in the range as red.
Here's the Code:

Sub CopyFiles()
Dim srcFOLDER As String
Dim tgtFOLDER As String
Dim fRNG      As Range
Dim fName     As Range
Dim BAD       As Boolean
srcFOLDER = ActiveSheet.Cells(4, 3)
tgtFOLDER = ActiveSheet.Cells(5, 3)   
 
Set fRNG = ActiveSheet.Range("E4:E2000").SpecialCells(xlConstants)
For Each fName In fRNG
If InStr(1, Dir(srcFOLDER), fName, vbTextCompare) Then 'Checking whether the file contain keywords in column
    
FileCopy srcFOLDER & "*" & fName & "*" & .Text, tgtFOLDER & "*" & fName & "*" & .Text 
 
Else
        fName.Interior.ColorIndex = 3
        BAD = True
    End If
Next fName
    
If BAD Then MsgBox "Some files were not found. These were highlighted for your reference."
End Sub

It works fine till it reaches copying section where I am getting error "Bad file name" or "Invalid Qualifier". If anyone could help correcting this one. Thanks.

 

[Jerry Sullivan]

Hi Parth13,

What would be typical values for srcFolder and fname that are read from your ranges?

Taking a step back before the FileCopy statement that is giving you an error, it looks like your keyword check might not be doing what you want either.

Dir(srcFOLDER) will return a string with the first filespec match (folder and path) that is found.

The first match might not match your fname keyword, but there could be other files in the folder that do match the fname keyword.

Those could be missed by your current code (depending on what you're using for srcFolder values).

To find additional matches use Dir()

 

[Parth13]

Hi Jerry,
Thanks for your reponse.
srcFolder refers to "D:\temp\"
While "fName" refers to different keywords which are to be searched one by one in srcFolder. e.g AFGAR, DCFER. Now these keywords can appear any where in file name. e.g 2145AFGARdd.pdf etc.
I'd the doubt that my keyword search code might be wrong. Would you please help correcting this?
Thanks,
Parth

 

[Jerry Sullivan]

Parth,

Below is some code you can try.

FileCopy does not allow you to specify source or destination parameters with Wildcards,
so the code loops through the folder finding matches with Dir() then copies the files with their full names.

Sub CopyFiles_Containing()
    Dim sSrcFolder As String, sTgtFolder As String, sFilename As String
    Dim c As Range, rPatterns As Range
    Dim bBad As Boolean
    
    sSrcFolder = ActiveSheet.Cells(4, 3)
    sTgtFolder = ActiveSheet.Cells(5, 3)
    
    Set rPatterns = ActiveSheet.Range("E4:E2000").SpecialCells(xlConstants)
    For Each c In rPatterns
        sFilename = Dir(sSrcFolder & "*" & c.Text & "*")
        If sFilename = "" Then
            c.Interior.ColorIndex = 3
            bBad = True
        Else
            While sFilename <> ""
                FileCopy sSrcFolder & sFilename, sTgtFolder & sFilename
                sFilename = Dir()
            Wend
        End If
    Next c
    If bBad Then MsgBox "Some files were not found. " & _
        "These were highlighted for your reference."
End Sub

 

[Parth13]

This worked flawless . Thanks a lot Jerry.
/Parth

 

[RobertRussell]

Hi,

This is almost perfect for a project I am working on at the moment.
The filenames I am working with are numbered pdf's (54325.pdf,3455pdf for example)
However, some of the pdf's have been labelled as follows 10343-10348 (mixed in with everything else) etc
I have been working with the solution above for quite some hours trying to figure out how I can check if the filename from rPatterns falls between the 2 numbers either side of the dash
Using InStr and InStrRev I can workout if the number I'm looking for falls between the filename, but I'm really struggling to fit it all together so that I can use the solution in this thread along with adding a check if the filename contains a dash and falls between 2 numbers
Any help would be much appreciated

Kind regards
Rob

 

[Jerry Sullivan]

Hi Rob,

Are all the files that you will be processing named with one of these two patterns:
nnn.pdf or nnn-nnn.pdf
...where nnn equals any whole number?

 

[RobertRussell]

Hi Jerry,

I have just revisited the folder, generally speaking they are nnn and nnn-nnn (some with a space either side of the dash) but there are some labelled nnn and nnn_(nnn) where the number in the brackets would usually be 01,02 or 001,002 etc when the pdf has more than 1 page.
Although I believe the original method posted should pick up the nnn type

Thanks
Rob

 

[Jerry Sullivan]

Rob, The code I previously suggested for the OP read a list of key phrases, then copied all files that had a partial match with those phrases.

sFilename = Dir(sSrcFolder & "*" & c.Text & "*")

This worked for the OP's purpose to find key phrases like AFGAR, DCFER in file names like 2145AFGARdd.pdf

Apply this to numbers may or may not work depending on whether you can specify key phrases that will not have undesired matches.

For example, if your key phrase is "3455" you will copy your intended target of "3455.pdf", but you could also copy unintended partial matches like
13455.pdf, 34552.pdf...

I'll be glad to try to help with some code, but I believe some constraints and assumptions need to be made for it to work as intended.

 

[RobertRussell]

Jerry, yes, it does pull out the correct pdf but can also select others if the number fits.

The format of the pdf' can be as follows:
12345.pdf
12345-12348.pdf
12345(1).pdf
12345_001.pdf

In some cases there may be a space before and or after the "-" and before the "("

With n being the whole number from the search list, would the following criteria work?
n.
n-
-n.
n(
n_
If not found with these, look through files with a "-" (12345-12348.pdf)

If n > n- and <n. Then a match has been found

I believe this should cover all eventualities

Hope I'm on the right lines here Jerry

Regards