Renaming PDF files based on Excel column Data

[Halley yenn]

Hi ,

I have a folder(D:/Test/) with 5 Pdf files with names as mentioned below
1) FY21 Employee Basic TET
2)FY21 Employee Basic BSD
3)FY21 Employee Basic GERT
4)FY21 Employee Basic KPIOT
5)FY21 Employee Basic SARD(common)
These Files need to renamed multiple times with the data present in an excel sheet as per Column C where the letter category is mentioned if an employee come under say TET then the pdf file need to renamed as EMPNAME_EMPID_FY21 Employee Basic TET.PDF and this should be done to 5K employees , employee may fall under any of these 4 categories and the last file is common file which need to be renamed like EMPNAME_EMPID_FY21 Employee Basic SARD.PDF for all the employees.
The data in the column is like
TET
BSD
GERT
KPIOT

Kindly help me resolve this as this need to be done bi-weekly.
Thanks in advance.

Halley

 

[JamesCanale]

Can't really fully test this, but:

MrExcelPlayground.xlsm
	A	B	C
1	Emp Name	Emp ID	Group
2	Emp1	a1	TET
3	Emp2	a2	TET
4	Emp3	a3	BSD
5	Emp4	a4	GERT
6	Emp5	a5	KPIOT
7	Emp6	a6	TET
8	Emp7	a7	BSD
9	Emp8	a8	GERT
10	Emp9	a9	TET
11	Emp10	a10	BSD
12	Emp11	a11	TET
13	Emp12	a12	BSD
14	Emp13	a13	GERT
15	Emp14	a14	KPIOT
16	Emp15	a15	TET
17	Emp16	a16	BSD
18	Emp17	a17	GERT
19	Emp18	a18	KPIOT
20	Emp19	a19	TET
21	Emp20	a20	BSD
22	Emp21	a21	TET
23	Emp22	a22	BSD
24	Emp23	a23	GERT
25	Emp24	a24	KPIOT
Sheet38

code:

Sub pdfcopier()
Dim path1 As String
Dim path2 As String
Dim filename1 As String
Dim filename2 As String
Dim SARD1 As String
Dim SARD2 As String

path1 = "D:\TEST\"
path2 = "D:\TEST\SomeOtherFolder\"
SARD1 = "FY21 Employee Basic SARD"

For t = 2 To 5000
    filename1 = "FY21 Employee Basic " & Cells(t, 3) & ".pdf"
    filename2 = Cells(t, 1) & "_" & Cells(t, 2) & "_FY21 Employee Basic " & Cells(t, 3) & ".pdf"
    SARD2 = Cells(t, 1) & "_" & Cells(t, 2) & "_FY21 Employee Basic SARD"

    FileCopy path1 & filename1, path2 & filename2
    FileCopy path1 & SARD1, path2 & SARD2
Next t
End Sub

 

[Halley yenn]

Can't really fully test this, but:

MrExcelPlayground.xlsm
	A	B	C
1	Emp Name	Emp ID	Group
2	Emp1	a1	TET
3	Emp2	a2	TET
4	Emp3	a3	BSD
5	Emp4	a4	GERT
6	Emp5	a5	KPIOT
7	Emp6	a6	TET
8	Emp7	a7	BSD
9	Emp8	a8	GERT
10	Emp9	a9	TET
11	Emp10	a10	BSD
12	Emp11	a11	TET
13	Emp12	a12	BSD
14	Emp13	a13	GERT
15	Emp14	a14	KPIOT
16	Emp15	a15	TET
17	Emp16	a16	BSD
18	Emp17	a17	GERT
19	Emp18	a18	KPIOT
20	Emp19	a19	TET
21	Emp20	a20	BSD
22	Emp21	a21	TET
23	Emp22	a22	BSD
24	Emp23	a23	GERT
25	Emp24	a24	KPIOT
Sheet38

code:

Sub pdfcopier()
Dim path1 As String
Dim path2 As String
Dim filename1 As String
Dim filename2 As String
Dim SARD1 As String
Dim SARD2 As String

path1 = "D:\TEST\"
path2 = "D:\TEST\SomeOtherFolder\"
SARD1 = "FY21 Employee Basic SARD"

For t = 2 To 5000
    filename1 = "FY21 Employee Basic " & Cells(t, 3) & ".pdf"
    filename2 = Cells(t, 1) & "_" & Cells(t, 2) & "_FY21 Employee Basic " & Cells(t, 3) & ".pdf"
    SARD2 = Cells(t, 1) & "_" & Cells(t, 2) & "_FY21 Employee Basic SARD"

    FileCopy path1 & filename1, path2 & filename2
    FileCopy path1 & SARD1, path2 & SARD2
Next t
End Sub

Hi,
Thanks for the prompt reply and sorry for late response I couldn't check this as I was out of station these days.
Just checked the code and it ran well, but I am getting errors where for some employees it says "No Letter"( Iam reffering to column C/3 from the worksheet you provided) where the code is stopping and one more thing there is one more column say column D where for some employees it shows date(say 03-04-2021) and I need to ignore them.

 

[Halley yenn]

Hi,
Thanks for the prompt reply and sorry for late response I couldn't check this as I was out of station these days.
Just checked the code and it ran well, but I am getting errors where for some employees it says "No Letter"( Iam reffering to column C/3 from the worksheet you provided) where the code is stopping and one more thing there is one more column say column D where for some employees it shows date(say 03-04-2021) and I need to ignore them.

please find the image for reference. Need to ignore both "No letter"(column C) and "withDate"(column D)