rename files in subfoldes based on multiple columns & headers

[KalilMe]

hi all

I have many subfolders in this directory "C:\Users\LP PC\Desktop\fl" each subfolders' names are the same thing in the headers so it should match headers with the subfolders and rename the files are existed in subfolders based on values are existed below the headers with considering the orginal files names it contains variety from digits and letters somtimes symmbols
I hope my thread is clear

1 Microsoft Excel .xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	PDF	JPG	PNG	GIF	AVI	DOCX	XLS	XLSM	XLSX	TEXT	MP3	MP4
2	PPD-1000	JPG-1000	PNG-AS1000	AS-GIF123	MOV-1	WR-SD-12	XL12-EXCEL1	XLM12-EXCEL	XLS13-EXCEL1	TX1-TXT1	MU_AS_12	VID_AS_12-23
3	PPD-1001	JPG-1001	PNG-AS1001	AS-GIF124	MOV-2	WR-SD-13	XL12-EXCEL2	XLM12-EXCEL	XLS13-EXCEL2	TX1-TXT2	MU_AS_13	VID_AS_12-24
4	PPD-1002	JPG-1002	PNG-AS1002	AS-GIF125	MOV-3	WR-SD-14	XL12-EXCEL3	XLM12-EXCEL	XLS13-EXCEL3	TX1-TXT3	MU_AS_14	VID_AS_12-25
5	PPD-1003	JPG-1003	PNG-AS1003	AS-GIF126	MOV-4	WR-SD-15	XL12-EXCEL4	XLM12-EXCEL	XLS13-EXCEL4	TX1-TXT4	MU_AS_15	VID_AS_12-26
6	PPD-1004	JPG-1004	PNG-AS1004	AS-GIF127	MOV-5	WR-SD-16	XL12-EXCEL5	XLM12-EXCEL	XLS13-EXCEL5	TX1-TXT5	MU_AS_16	VID_AS_12-27
7	PPD-1005	JPG-1005	PNG-AS1005	AS-GIF128	MOV-6	WR-SD-17	XL12-EXCEL6	XLM12-EXCEL	XLS13-EXCEL6	TX1-TXT6	MU_AS_17	VID_AS_12-28
8	PPD-1006	JPG-1006	PNG-AS1006	AS-GIF129	MOV-7	WR-SD-18	XL12-EXCEL7	XLM12-EXCEL	XLS13-EXCEL7	TX1-TXT7	MU_AS_18	VID_AS_12-29
9	PPD-1007	JPG-1007	PNG-AS1007	AS-GIF130	MOV-8	WR-SD-19	XL12-EXCEL8	XLM12-EXCEL	XLS13-EXCEL8	TX1-TXT8	MU_AS_19	VID_AS_12-30
10	PPD-1008	JPG-1008	PNG-AS1008	AS-GIF131	MOV-9	WR-SD-20	XL12-EXCEL9	XLM12-EXCEL	XLS13-EXCEL9	TX1-TXT9	MU_AS_20	VID_AS_12-31
11	PPD-1009	JPG-1009	PNG-AS1009	AS-GIF132	MOV-10	WR-SD-21	XL12-EXCEL10	XLM12-EXCEL	XLS13-EXCEL10	TX1-TXT10	MU_AS_21	VID_AS_12-32
12	PPD-1010	JPG-1010	PNG-AS1010	AS-GIF133	MOV-11	WR-SD-22	XL12-EXCEL11	XLM12-EXCEL	XLS13-EXCEL11	TX1-TXT11	MU_AS_22	VID_AS_12-33
13	PPD-1011	JPG-1011	PNG-AS1011	AS-GIF134	MOV-12	WR-SD-23	XL12-EXCEL12	XLM12-EXCEL	XLS13-EXCEL12	TX1-TXT12	MU_AS_23	VID_AS_12-34
14	PPD-1012	JPG-1012	PNG-AS1012	AS-GIF135	MOV-13	WR-SD-24	XL12-EXCEL13	XLM12-EXCEL	XLS13-EXCEL13	TX1-TXT13	MU_AS_24	VID_AS_12-35
1

 

[KalilMe]

any assistance experts?

 

[Sequoyah]

Hi KalilMe,
if you don't have enough knowledge of VBA I suggest you to use a file renaming software for this purpose. As an exercise I propose a solution, I have not not added error handling, so check carefully that your list does not include duplicate names or file names that already exist in your folder.

Sub rename5()
    
'https://www.mrexcel.com/board/threads/rename-files-in-subfoldes-based-on-multiple-columns-headers.1173602/
    
    Const MYPATH As String = "C:\Users\LP PC\Desktop\fl\"
        
    Dim LastCol As Integer, LastRow As Long
    Dim i As Integer, j As Integer
    Dim NewName As String, FileExt As String, OldName As String
    Dim FileName As String, FileList() As String, lCount As Long
    
    LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    
    For j = 1 To LastCol
        LastRow = ActiveSheet.Cells(Rows.Count, j).End(xlUp).Row
        FileExt = Cells(1, j).Value
        
        lCount = 0
        FileName = Dir(MYPATH & FileExt & "\")
        While Len(FileName) > 1
            lCount = lCount + 1
            ReDim Preserve FileList(1 To lCount)
            FileList(lCount) = FileName
            FileName = Dir()
        Wend
        
        For i = 2 To LastRow
            
            NewName = MYPATH & FileExt & "\" & Cells(i, j).Value & "." & FileExt
            OldName = MYPATH & FileExt & "\" & FileList(i - 1)
            
            Name OldName As NewName
            
        Next i
        
    Next j
    
    MsgBox "Done!"
    
End Sub

 

[KalilMe]

first my apologies to answering you
second thanks
third it give error "subscript out of range "

 OldName = MYPATH & FileExt & "\" & FileList(i - 1)

and I 'm sure to matching the names of folders with the names of headers

 

[Sequoyah]

Hi KalilMe,
thanks for the feedback. I made many tests before posting and I have not encountered the problem you report. Does any of the files in your folders get renamed?

 

[KalilMe]

Does any of the files in your folders get renamed?

yes it renames files PDF in subfolder name is PDF