Copy and rename a file with partial name start and end name

[anichols]

Hello all,

I typically move a number of files using VBA using very simple code (sometimes its FileCopy instead of NameAS):

Name Sheets(2).Range("F26").Value As Sheets(2).Range("F27").Value

The cells values that the code references typically look like this: "C:\Users\drew\Desktop\REPORTING\test01.txt"
Though in some cases there are formulas that drive the particular file name.

Now, I have a new batch of files to move around, but I've encountered a problem, and various things online don't quite seem to be working for me. Now the folder that these file are in stays consistent, but there are unknown characters in each file name. The First part of the file name would be something like: "GL Print", and the end of the file name will be "07-29-2021.pdf" and the middle is unknown. My challenge is that there are dozens of files that have the same Prefix, and dozens also that have the same Suffix. But one file will be the correct file, and once found, I will need to NameAs the file to a new location and name.

Any help getting this right would be greatly appreciated.

 

[John_w]

Try something like this:

    Dim sourceFolder As String, fileName As String
    sourceFolder = "C:\folder\path\"
    fileName = Dir(sourceFolder & "GL Print*07-29-2021.pdf")
    If fileName <> vbNullString Then
        Name sourceFolder & fileName As "C:\dest\folder\" & fileName
    Else
        MsgBox "File not found"
    End If

 

[anichols]

I keep getting file not found when I try this. I was playing with something like this, but it's not working either.

Dim Cell01, Cell02, Cell03, Cell04 As Long
Cell01 = Sheets(2).Range("F26").Value 'Folder Location
Cell02 = Sheets(2).Range("I26").Value 'Known Prefix
Cell03 = Sheets(2).Range("I27").Value 'Known suffix & file extension
Cell04 = Sheets(2).Range("F27").Value 'new filepath and name
FileCopy Cell01 & Cell02 & "*" & Cell03, Cell04

 

[John_w]

You can't use wildcards with FileCopy. Use wildcards with Dir to first find the actual file name.

Try this (untested):

Dim Cell01 As String, Cell02 As String, Cell03 As String, Cell04 As String
Cell01 = Sheets(2).Range("F26").Value 'Folder Location
Cell02 = Sheets(2).Range("I26").Value 'Known Prefix
Cell03 = Sheets(2).Range("I27").Value 'Known suffix & file extension
Cell04 = Sheets(2).Range("F27").Value 'new filepath and name
Dim fileName As String
If Right(Cell01,1) <> "\" Then Cell01 = Cell01 & "\"
fileName = Dir(Cell01 & Cell02 & "*" & Cell03)
If fileName <> vbNullString Then
    FileCopy Cell01 & fileName, Cell04
Else
    MsgBox "File not found"
End If

 

[anichols]

You can't use wildcards with FileCopy. Use wildcards with Dir to first find the actual file name.

Try this (untested):

Dim Cell01 As String, Cell02 As String, Cell03 As String, Cell04 As String
Cell01 = Sheets(2).Range("F26").Value 'Folder Location
Cell02 = Sheets(2).Range("I26").Value 'Known Prefix
Cell03 = Sheets(2).Range("I27").Value 'Known suffix & file extension
Cell04 = Sheets(2).Range("F27").Value 'new filepath and name
Dim fileName As String
If Right(Cell01,1) <> "\" Then Cell01 = Cell01 & "\"
fileName = Dir(Cell01 & Cell02 & "*" & Cell03)
If fileName <> vbNullString Then
    FileCopy Cell01 & fileName, Cell04
Else
    MsgBox "File not found"
End If

Awesome! That worked perfectly, thank you!