rename files in subfoldes based on multiple columns & headers

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
122
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKL
1PDFJPGPNGGIFAVIDOCXXLSXLSMXLSXTEXTMP3MP4
2PPD-1000JPG-1000PNG-AS1000AS-GIF123MOV-1WR-SD-12XL12-EXCEL1XLM12-EXCELXLS13-EXCEL1TX1-TXT1MU_AS_12VID_AS_12-23
3PPD-1001JPG-1001PNG-AS1001AS-GIF124MOV-2WR-SD-13XL12-EXCEL2XLM12-EXCELXLS13-EXCEL2TX1-TXT2MU_AS_13VID_AS_12-24
4PPD-1002JPG-1002PNG-AS1002AS-GIF125MOV-3WR-SD-14XL12-EXCEL3XLM12-EXCELXLS13-EXCEL3TX1-TXT3MU_AS_14VID_AS_12-25
5PPD-1003JPG-1003PNG-AS1003AS-GIF126MOV-4WR-SD-15XL12-EXCEL4XLM12-EXCELXLS13-EXCEL4TX1-TXT4MU_AS_15VID_AS_12-26
6PPD-1004JPG-1004PNG-AS1004AS-GIF127MOV-5WR-SD-16XL12-EXCEL5XLM12-EXCELXLS13-EXCEL5TX1-TXT5MU_AS_16VID_AS_12-27
7PPD-1005JPG-1005PNG-AS1005AS-GIF128MOV-6WR-SD-17XL12-EXCEL6XLM12-EXCELXLS13-EXCEL6TX1-TXT6MU_AS_17VID_AS_12-28
8PPD-1006JPG-1006PNG-AS1006AS-GIF129MOV-7WR-SD-18XL12-EXCEL7XLM12-EXCELXLS13-EXCEL7TX1-TXT7MU_AS_18VID_AS_12-29
9PPD-1007JPG-1007PNG-AS1007AS-GIF130MOV-8WR-SD-19XL12-EXCEL8XLM12-EXCELXLS13-EXCEL8TX1-TXT8MU_AS_19VID_AS_12-30
10PPD-1008JPG-1008PNG-AS1008AS-GIF131MOV-9WR-SD-20XL12-EXCEL9XLM12-EXCELXLS13-EXCEL9TX1-TXT9MU_AS_20VID_AS_12-31
11PPD-1009JPG-1009PNG-AS1009AS-GIF132MOV-10WR-SD-21XL12-EXCEL10XLM12-EXCELXLS13-EXCEL10TX1-TXT10MU_AS_21VID_AS_12-32
12PPD-1010JPG-1010PNG-AS1010AS-GIF133MOV-11WR-SD-22XL12-EXCEL11XLM12-EXCELXLS13-EXCEL11TX1-TXT11MU_AS_22VID_AS_12-33
13PPD-1011JPG-1011PNG-AS1011AS-GIF134MOV-12WR-SD-23XL12-EXCEL12XLM12-EXCELXLS13-EXCEL12TX1-TXT12MU_AS_23VID_AS_12-34
14PPD-1012JPG-1012PNG-AS1012AS-GIF135MOV-13WR-SD-24XL12-EXCEL13XLM12-EXCELXLS13-EXCEL13TX1-TXT13MU_AS_24VID_AS_12-35
1
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Sequoyah

Board Regular
Joined
Mar 26, 2017
Messages
60
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.
VBA Code:
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

Board Regular
Joined
Mar 5, 2021
Messages
122
Office Version
  1. 2016
Platform
  1. Windows
first my apologies to answering you
second thanks
third it give error "subscript out of range "
VBA Code:
 OldName = MYPATH & FileExt & "\" & FileList(i - 1)
and I 'm sure to matching the names of folders with the names of headers
 

Sequoyah

Board Regular
Joined
Mar 26, 2017
Messages
60
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?
 

Forum statistics

Threads
1,141,075
Messages
5,704,156
Members
421,330
Latest member
imdumb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top