Rename pdf files with the data saved in excel using macro

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Rename pdf files with the data saved in excel using macro

Post by Jude0887 »

Hello Hans,

I need your help on this

I request you to please provide me a macro

I do have 100 pdf files with file name 1.pdf, 2.pdf, 3.pdf etc and it goes on.
Idea is to have the old file name of pdf In column A and in columb B with the Right details which will replace the old file name.

Column A & Column B will have the details

Columb B should is the actual detail name by which the pdf has to be renamed

Kindly pl provide me with a coding

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Rename pdf files with the data saved in excel using macro

Post by HansV »

Welcome to Eileen's Lounge!

Try this macro:

Code: Select all

Sub RenamePDF()
    ' Change the path as needed, keeping the \ at the end
    Const strFolder = "C:\PDF\"
    Dim r As Long
    Dim m As Long
    On Error GoTo ErrHandler
    ' Get the last non-empty row
    m = Range("A" & Rows.Count).End(xlUp).Row
    ' Assuming that the names start in row 2
    For r = 2 To m
        Name strFolder & Range("A" & r).Value As strFolder & Range("B" & r).Value
    Next r
    Exit Sub
ErrHandler:
    MsgBox "There was a problem renaming " & Range("A" & r).Value & " to " & Range("B" & r).Value, vbExclamation
    Resume Next
End Sub
Best wishes,
Hans

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Rename pdf files with the data saved in excel using macro

Post by Jude0887 »

Thank you for your valuable time and coding
When i an about to Run i am getting an error message station “ There was a problem renaming File name in Column A to Column B. Kindly advise Hans

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Rename pdf files with the data saved in excel using macro

Post by HansV »

In the first place, make sure that you change the line

Code: Select all

    Const strFolder = "C:\PDF\"
Replace C:\PDF with the path of the folder containing the files.

In the second place, I assumed that the names start in row 2:

Code: Select all

    For r = 2 To m
If the names start further down, for example in row 5, change that line accordingly:

Code: Select all

    For r = 5 To m
Best wishes,
Hans

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Rename pdf files with the data saved in excel using macro

Post by Jude0887 »

Thank you for your reply Hans
Just because we have the errhandler
ErrHandler:
MsgBox "There was a problem renaming " & Range("A" & r).Value & " to " & Range("B" & r).Value, vbExclamation

It everytime throws a message that there was a problem renaming from A to B when the macro is run

And we need to keep on clicking Ok .. OK... Ok for the number of files available. Is it possible if it directly executes the macro when we run without and pdf files in folder gets renamed without our clicks

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Rename pdf files with the data saved in excel using macro

Post by HansV »

You could change the line

Code: Select all

    On Error GoTo ErrHandler
to

Code: Select all

    On Error Resume Next
but I fear that no files will be renamed...
Best wishes,
Hans

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Rename pdf files with the data saved in excel using macro

Post by Jude0887 »

Okay let me try and let u know Hans

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Rename pdf files with the data saved in excel using macro

Post by Jude0887 »

Yea you are right its now working properly.. the previous one was good. I have two queries Hans... first one suppose say for example if i have in column B Same values. Only one pdf is getting renamed and other is not getting renamed

Example
Cloumn A Column B
1.pdf 200
2.pdf 200

After running macro only one file i.e 1.pdf is renamed to 200, wherein the other file 2.pdf remains renamed

Second one These are pdf files once it get renamed it looses its file format is there any way i can add in coding to rename it in the same pdf format

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Rename pdf files with the data saved in excel using macro

Post by HansV »

1) You should not have the same name in multiple cells in column B. Each name should be unique.
2) The names in column B should end in .pdf, just like the names in column A.
Best wishes,
Hans

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Rename pdf files with the data saved in excel using macro

Post by Jude0887 »

Thanks Hans. MUch appreciated

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Rename pdf files with the data saved in excel using macro

Post by Jude0887 »

Hans is it possible to get it done by macro

1. Expecting the file to be renamed in pdf
2. If two or three similar values in column then it should rename as 10(1), 10(2) ... 10(3)

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Rename pdf files with the data saved in excel using macro

Post by HansV »

Try this version. Don't forget to edit the path in strFolder.

Code: Select all

Sub RenamePDF()
    ' Change the path as needed, keeping the \ at the end
    Const strFolder = "C:\PDF\"
    Dim r As Long
    Dim m As Long
    Dim strOld As String
    Dim strNew As String
    Dim n As Long
    On Error Resume Next
    ' Get the last non-empty row
    m = Range("A" & Rows.Count).End(xlUp).Row
    ' Assuming that the names start in row 2
    For r = 2 To m
        strOld = Range("A" & r).Value
        strNew = Range("B" & r).Value
        n = Application.CountIf(Range("B1:B" & r - 1), strNew)
        If n > 0 Then
            strNew = strNew & "(" & n & ")"
        End If
        Name strFolder & strOld As strFolder & strNew & ".pdf"
    Next r
End Sub
Best wishes,
Hans

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Rename pdf files with the data saved in excel using macro

Post by Jude0887 »

Thank u Hans its working fine