Rename files based on if condition

Question

I have what might be a simple query. I need to rename my files based on the below list.

Column A    Column B
00145         AB12
00206         AZ15
00705         AK09
so on ...........

Currently my files are named as according to Column A and I need to batch rename them to the names in column B. There are over a thousand records.

Which means the file "00145" will be renamed to "AB12", "00206" to "AZ15" and so on.

I have thought about sorting them in excel and then use a rename list in a filerenamer software, however the challenge is that the files in the folder might not be necessarily in order i.e. file no. "00705" may be missing so by using a rename list all the subsequent file names will be off by one.

What I need is something to identify the files that are available in the list and in the folder and then match them to the corresponding name and rename the files accordingly.

Any help will be highly appreciated.

Thank you.

Answer 1

just tested

Sub rename_batch()

filePath = "C:\tmp\"
counter = 0

For Each c In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))

  If Dir(filePath & c.Value) <> "" Then
    Name filePath & c.Value As filePath & c.Offset(counter, 1).Value
    c.Offset(0, 2).Value = c.Value & " > " & c.Offset(counter, 1).Value
  Else
    counter = counter - 1
  End If

Next

End Sub

this will go down the list in column "A" starting from "A2" onwards, it checks for file, if file exists it will rename to whatever is in column "B", if file is missing it skips the line but the counter goes down, which is the offset on where to read from column "B", so it will rename to a file earlier in the column "B". I have also included a line so it populates column "C" with which file it renamed to what. P.S. bear in mind i have not included any checks to see if destination file exists. Let me know if you would need that too