I have what might be a simple query. I need to rename my files based on the below list.
我有一个简单的查询。我需要根据下面的列表重命名我的文件。
Column A Column B
00145 AB12
00206 AZ15
00705 AK09
so on ...........
Currently my files are named as according to Column A and I need to batch rename them to the names in column B. There are over a thousand records.
目前我的文件是按照A列命名的，我需要批量重命名为b列中的名称。
Which means the file "00145" will be renamed to "AB12", "00206" to "AZ15" and so on.
这意味着文件“00145”将被重命名为“AB12”、“00206”和“AZ15”等等。
I have thought about sorting them in excel and then use a rename list in a filerenamer software, however the challenge is that the files in the folder might not be necessarily in order i.e. file no. "00705" may be missing so by using a rename list all the subsequent file names will be off by one.
我已经考虑过在excel中对它们进行排序，然后在filerenamer软件中使用重命名列表，但是挑战是文件夹中的文件不一定要按顺序排列，即file no。“00705”可能会丢失，所以通过使用重命名列表，所有后续的文件名将被删除一个。
What I need is something to identify the files that are available in the list and in the folder and then match them to the corresponding name and rename the files accordingly.
我需要一些东西来识别列表和文件夹中可用的文件，然后将它们匹配到相应的名称并相应地重命名文件。
Any help will be highly appreciated.
非常感谢您的帮助。
Thank you.
谢谢你！
1 个解决方案
#1
1
just tested
只是测试
Sub rename_batch()
filePath = "C:\tmp\"
counter = 0
For Each c In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
If Dir(filePath & c.Value) <> "" Then
Name filePath & c.Value As filePath & c.Offset(counter, 1).Value
c.Offset(0, 2).Value = c.Value & " > " & c.Offset(counter, 1).Value
Else
counter = counter - 1
End If
Next
End Sub
this will go down the list in column "A" starting from "A2" onwards, it checks for file, if file exists it will rename to whatever is in column "B", if file is missing it skips the line but the counter goes down, which is the offset on where to read from column "B", so it will rename to a file earlier in the column "B". I have also included a line so it populates column "C" with which file it renamed to what. P.S. bear in mind i have not included any checks to see if destination file exists. Let me know if you would need that too
这将会降低列的列表中“A”从“A2”开始,它检查文件,如果文件存在,它将重命名任何列“B”,如果文件丢失它跳过了线,但计数下降,抵消在哪里阅读的列“B”,所以它将重命名一个文件早些时候列“B”。我还包含了一行，因此它填充了“C”列，并将其重命名为什么。请记住，我没有包括任何检查目标文件是否存在。如果你也需要的话，请告诉我
#1
1
just tested
只是测试
Sub rename_batch()
filePath = "C:\tmp\"
counter = 0
For Each c In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
If Dir(filePath & c.Value) <> "" Then
Name filePath & c.Value As filePath & c.Offset(counter, 1).Value
c.Offset(0, 2).Value = c.Value & " > " & c.Offset(counter, 1).Value
Else
counter = counter - 1
End If
Next
End Sub
this will go down the list in column "A" starting from "A2" onwards, it checks for file, if file exists it will rename to whatever is in column "B", if file is missing it skips the line but the counter goes down, which is the offset on where to read from column "B", so it will rename to a file earlier in the column "B". I have also included a line so it populates column "C" with which file it renamed to what. P.S. bear in mind i have not included any checks to see if destination file exists. Let me know if you would need that too
这将会降低列的列表中“A”从“A2”开始,它检查文件,如果文件存在,它将重命名任何列“B”,如果文件丢失它跳过了线,但计数下降,抵消在哪里阅读的列“B”,所以它将重命名一个文件早些时候列“B”。我还包含了一行，因此它填充了“C”列，并将其重命名为什么。请记住，我没有包括任何检查目标文件是否存在。如果你也需要的话，请告诉我