Renaming PDFs based on a reference number

[Benjam26]

Hey all!

I'm looking for some help regarding the renaming of lots of PDF files. The PDFs are named like 042043341_38662557_0_96.pdf. The first part (042043341) is a unique reference number which is also situated in column D, the rest is not unique and is related to an order number.

I found this thread Rename all .pdf files in folder where the last page has the macro

Sub Shahzadt()
Dim z As Long, e As Long, g As Long, x As Long
Dim f As String
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 8).Select
f = Dir(Cells(1, 2) & "*.pdf")
    Do While Len(f) > 0
    ActiveCell.Formula = f
    ActiveCell.Offset(1, 0).Select
    f = Dir()
    Loop
z = Cells(Rows.Count, 8).End(xlUp).Row
x = Cells(Rows.Count, 1).End(xlUp).Row
    For e = 2 To z
        For g = 2 To x
            If InStr(Cells(e, 8), Cells(g, 1)) > 0 Then
            Name Cells(1, 2) & Cells(e, 1) As Cells(1, 2) & Cells(g, 1) & Cells(g, 2) & Cells(g, 3)
            End If
        Next g
    Next e
MsgBox "Renaming is complete."
End Sub

I have tried to make this work with my data but I am having a struggle interpreting the code. The writer mentions the code will search in column A for a match of a part of the PDF name and if it is a match it will rename as columns A, B & C but I do not understand where in the code it relates to each of these fields.

I hope I have made some sense and appreciate any help you could offer.

Thanks!

 

[dougmarkham]

Hi Benjam26,

I have to rename lots of images in my job. I've already researched lots of VBA to do this and decided to use freeware instead. To rename my files, I create an output-text-file which feeds into a very nifty little piece of freeware called Bulk Rename Utility.

Bulk Rename Utility - Free File Renaming Software
This tool is awesome and I suggest you save yourself the code debugging and use this instead.

All you need to do is sort an excel table that has 4 columns with the following headers
Col A = Current filename
Col B = Pipe Delimiter
Col C = New filename
Col D = Formula column

Add the name change data and add a pipe-delimiter for every row in column B. Maybe make the table dynamic if you're talking hundreds of files i.e., so the following formula fills down automatically.

Then in Column D (D2), add the formula =CONCATENATE(A2,"|",B2)
The two filenames must be separated by a pipe-delimiter. Fill the formula down, then copy column D and paste as values so that column D contains rows for instance:

Old name.pdf|New name.pdf
Old name.pdf|New name.pdf
Old name.pdf|New name.pdf

etc.

Then, copy the rows in Column D (not including headers) and paste into a notepad window. If you find the Old name|New name rows are in speech marks after pasting into notepad eg. "Old name.pdf|New name.pdf", hit "ctrl + A" (select all) and go to Find & Replace in notepad and replace " with nothing to remove them. Save the file as rename.txt.

Then you Open the Bulk rename utility. Go to Windows explorer and navigate to your PDF files. Copy the FilePath where your PDF files are stored and paste it into the FilePath box at the top of the Bulk Rename Utility program and hit the Enter key (make sure there is a back-slash at the end of the FilePath. This will bring up all your PDF files in the main window.

Then go to the file menu and select the Actions drop-down: click on 'Import Rename-Pairs'. Navigate to where you stored your rename.txt and select it (an orange box will come up showing that you've loaded the rename.txt file.

Then go to the main window and with your mouse, select the PDF files (hold ctrl and select files, or, if it's a continuous list, hold shift key and select all files).
You should now see the new names in green in the column New Name.

Hit the rename button at the bottom-right corner of the program (but make sure before doing this that you have closed any PDF files).

Job should be a good one.

Kind regards,

Doug.

 

[Benjam26]

Hi Benjam26,

I have to rename lots of images in my job. I've already researched lots of VBA to do this and decided to use freeware instead. To rename my files, I create an output-text-file which feeds into a very nifty little piece of freeware called Bulk Rename Utility.
...

Job should be a good one.

Kind regards,

Doug.

Hey Doug!

Thank you for your response on this. It looks like a great option to use so will be working on that this weekend to get the results I need.

I don't actually have a column of current file names but I am sure I can use something like a left search for "_" to take the part I need. Will also need a way to get all the file names into excel but that is the least of my worries with this and I am sure I can find myself a way to do that.

Thanks again, I'll reply once I have given it a go

 

[dougmarkham]

Hey Doug!

Thank you for your response on this. It looks like a great option to use so will be working on that this weekend to get the results I need.

I don't actually have a column of current file names but I am sure I can use something like a left search for "_" to take the part I need. Will also need a way to get all the file names into excel but that is the least of my worries with this and I am sure I can find myself a way to do that.

Thanks again, I'll reply once I have given it a go

Hi Benjam26,

Glad this will help.

With regards to getting a list of filenames into excel, I have a bunch of solutions.
The latest I discovered from an amazing Greek VBA enthusiast who has turned his VBA model into an .exe program
Folders, Subfolders & Files - Freeware Program – My Engineering World

I've tested it, and it works a treat.

The other method I use is a file lister comes from this: List the Files in a Folder with the FileSystemObject
Yet another method is a fab little program that can create a csv file of the contents of any folder:
SysExporter: Grab data from list-view, tree-view, combo box, WebBrowser control, and text-box.

Best of luck,

Doug.

 

[Benjam26]

Hi Benjam26,

Glad this will help.

With regards to getting a list of filenames into excel, I have a bunch of solutions.
The latest I discovered from an amazing Greek VBA enthusiast who has turned his VBA model into an .exe program
Folders, Subfolders & Files - Freeware Program – My Engineering World

I've tested it, and it works a treat.

The other method I use is a file lister comes from this: List the Files in a Folder with the FileSystemObject
Yet another method is a fab little program that can create a csv file of the contents of any folder:
SysExporter: Grab data from list-view, tree-view, combo box, WebBrowser control, and text-box.

Best of luck,

Doug.

Hi Doug!

Just wanted to say a massive thank you for your help! Everything you linked/told me about work perfectly and gave me all the results I was after.

Need to clean up the way I got to the results to streamline it a bit more but this is incredible already!

Thanks again