Rename pdf files with the data saved in excel using macro
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Rename pdf files with the data saved in excel using macro
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
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
-
- Administrator
- Posts: 78488
- 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
Welcome to Eileen's Lounge!
Try this macro:
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
Hans
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Rename pdf files with the data saved in excel using macro
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
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
-
- Administrator
- Posts: 78488
- 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
In the first place, make sure that you change the line
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:
If the names start further down, for example in row 5, change that line accordingly:
Code: Select all
Const strFolder = "C:\PDF\"
In the second place, I assumed that the names start in row 2:
Code: Select all
For r = 2 To m
Code: Select all
For r = 5 To m
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Rename pdf files with the data saved in excel using macro
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
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
-
- Administrator
- Posts: 78488
- 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
You could change the line
to
but I fear that no files will be renamed...
Code: Select all
On Error GoTo ErrHandler
Code: Select all
On Error Resume Next
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Rename pdf files with the data saved in excel using macro
Okay let me try and let u know Hans
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Rename pdf files with the data saved in excel using macro
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
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
-
- Administrator
- Posts: 78488
- 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
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.
2) The names in column B should end in .pdf, just like the names in column A.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Rename pdf files with the data saved in excel using macro
Thanks Hans. MUch appreciated
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Rename pdf files with the data saved in excel using macro
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)
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)
-
- Administrator
- Posts: 78488
- 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
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
Hans
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Rename pdf files with the data saved in excel using macro
Thank u Hans its working fine