Rename files using criteria in Excel

[RosBosss1]

I have a batch of files that need renaming. The files come named with an 8-digit account number at the start of the filename, like "12345678_Report.pdf."

I have an Excel spreadsheet that lists all account numbers and the corresponding client names.

Is there a way to use the old file name to match / lookup the account number tied to the client name, and then rename the old file name with the client name instead of the account number?

Example:

Old Filename to be renamed: "12345678_Report.pdf"

Excel spreadsheet lookup: multiple account numbers, with "12345678" in column A and "Smith, John" in Column B,

New filename should be "Smith, John_Report.pdf." This would be done for a batch of files.

Thank you!

[Kenneth Hobs]

Welcome to the forum?

Are all of the numbers unique? If not, how would you handle account numbers with different account names?

[RosBosss1]

All account numbers are unique 8-digit numbers tied to clients and not repeated, although clients may have more than one account, so client names can be repeated.

[JBeaucaire]

Try this on a small folder of PDFs, update the macro to point to that fPATH.


Option Explicit

Sub BatchRenamePDF()
Dim fPATH As String, fNAME As String
Dim LookUpTable As Range, MyCode As Long, MyName As String

fPATH = "C:\Path\To\Find\PDFs\" 'remember the final \ in this folder name
fNAME = Dir(fPATH & "*.pdf") 'get first filename

Set LookUpTable = ThisWorkbook.Sheets("Sheet1").Range("A:B") 'codes in A, names in B
On Error Resume Next

Do While Len(fNAME) > 0 'only proceed while new names are found
MyCode = CLng(Left(fNAME, 8)) 'extract the code, lookup the name in the table
MyName = WorksheetFunction.VLookup(MyCode, LookUpTable, 2, 0)
If MyName = "" Then MyName = WorksheetFunction.VLookup(MyCode & "", LookUpTable, 2, 0) 'backup search using text search
If MyName <> "" Then 'rename the file if the code was looked up successfully
Name fPATH & fNAME As fPATH & Replace(fNAME, MyCode, MyName)
MyName = ""
End If

fNAME = Dir 'get the next filename
Loop

End Sub

[RosBosss1]

I tried this and I must be doing something wrong - I entered the code in the VBA for the worksheet containing the Lookup, and I edited the FPath to point to a test group of files. When I run the code, a blank UserForm box pops up and nothing else happens. Thanks in advance, I am not a developer, as you might guess!

[JBeaucaire]

Definitely something is amiss since my code does not use popups or userforms in any way. You can post the desensitized workbook and I can examine the code as it is installed and possibly offer a suggestion.

To attach a file, GO ADVANCED and use the paperclip icon.

[RosBosss1]

See attached sample file with the VBA code. Thank you!

[JBeaucaire]

Interesting. Somehow a blank FORM was created in your project and the macro hidden in there. I removed the form and put the macro into standard code module (Module1). Then I added a button to your sheet and connected the macro to it to make running it simplest.

I can't test it on my system, but the original version I gave did work, so I see no reason your edits shouldn't work as well. Give this a try.

[RosBosss1]

I appreciate the help a great deal. Unfortunately it doesn't seem to be working for me. Nothing happens when I click the button, or even run it from the Developer ribbon.

[JBeaucaire]

The only reason a simple rename macro like this would do nothing is if the filenames in the master file are not being found.


Try removing the ON ERROR RESUME NEXT line of code, that way the macro will fail and you can use the DEBUG option to examine the line that is failing, hover your mouse over the variables to see if anything is not "exactly" right.

You can also post your edited version here and I can review your tweaks.

[JBeaucaire]

Even though the lookup codes are 8-digit numbers, in filenames they are actually strings. So I made some tweaks to the variable declarations, and here is a video of this process actually working on my system.

https://www.screencast.com/t/2k3klFLDg


Option Explicit

Sub BatchRenamePDF()
Dim fPATH As String, fNAME As String
Dim LookUpTable As Range, MyCode As String, MyName As String

fPATH = "C:\TEMP\PDF\06-28-2016\" 'remember the final \ in this folder name
fNAME = Dir(fPATH & "*.pdf") 'get first filename

Set LookUpTable = ThisWorkbook.Sheets("Sheet1").Range("A:B") 'codes in A, names in B
On Error Resume Next

Do While Len(fNAME) > 0 'only proceed while new names are found
MyCode = Left(fNAME, 8) 'extract the code, lookup the name in the table
MyName = WorksheetFunction.VLookup(MyCode, LookUpTable, 2, 0)
If MyName = "" Then MyName = WorksheetFunction.VLookup(CLng(MyCode), LookUpTable, 2, 0) 'backup search using text search
If MyName <> "" Then 'rename the file if the code was looked up successfully
Name fPATH & fNAME As fPATH & Replace(fNAME, MyCode, MyName)
MyName = ""
End If

fNAME = Dir 'get the next filename
Loop

End Sub