Using Excel VBA to rename files in directory

[samilynn]

is it possible to have a spreadsheet with two columns, Col A showing a list of current file names in a particular directory, and Col B the names I want these files to be renamed to. Is there some code that I can use to do this, or do I have to rename these files one by one until I get old?

Thanks,

Samantha

 

[Harry Flashman]

When I replied to this thread some years ago I posted a short video showing how to enter the code in my posts. I have received a few automatic requests to re-share the video (evidently due to security updates this is necessary from time to time).
I have now re-shared the video if anyone wants it.
Scroll back through the thread to see the code.
Google Drive: Sign-in

 

[Harry Flashman]

When I replied to this thread some years ago I posted a short video showing how to enter the code in my posts. I have received a few automatic requests to re-share the video (evidently due to security updates this is necessary from time to time).
I have now re-shared the video if anyone wants it.
Scroll back through the thread to see the code.
Google Drive: Sign-in

Sorry that link might not work. Try this one.

Rename Files Using VBA.mp4

drive.google.com

 

[mik1996]

Hi, this is really similar to what i need in work, the only difference being the files that need renaming are in multiple folders and subfolders, is there any way the listfiles/filenametoexcel macro can be amended so you can select a directory/folder rather than an individual sheet? any help would be much appreciated, thanks

 

[saraguchi2316]

i encounter run-time error '53' file not found when execute rename_files().When i click debug,it link me to

Name Cells(r,"A") As Cells(r,"B")

The issue is that the file path isn't included. Just change the ReName_Files macro to the below (and make sure to specify the path)

Sub ReName_Files()

Dim r As Integer
[B]Dim path As String

path = "Y:\General\Test Path\" ' ---> Change to your path[/B]
r = 1
Do Until IsEmpty(Cells(r, "A")) Or IsEmpty(Cells(r, "B"))
Name [B]path &[/B] Cells(r, "A") As[B] path &[/B] Cells(r, "B")
r = r + 1
Loop
End Sub

 

[saraguchi2316]

Hi, this is really similar to what i need in work, the only difference being the files that need renaming are in multiple folders and subfolders, is there any way the listfiles/filenametoexcel macro can be amended so you can select a directory/folder rather than an individual sheet? any help would be much appreciated, thanks

Not sure if this is helpful, but if you create an excel list you can just use the ReName_Files macro to rename, just make sure you include the path as part of the file name

So for example, if your excel has the below in it

Cell A1: C:\Users\Downloads\Test File.pdf
Cell B1: C:\Users\Downloads\This Is the same file.pdf

Your 'Test File.pdf' saved in your downloads folder would be renamed to 'This is the same file.pdf'

Sub ReName_Files()
Dim r As Integer
r = 1
Do Until IsEmpty(Cells(r, "A")) Or IsEmpty(Cells(r, "B"))
Name Cells(r, "A") As Cells(r, "B")
r = r + 1
Loop
MsgBox "All your old file names in Column 'A' have been reNamed" & vbCr & _
        "to the adjacent new name in column 'B'."
End Sub

 

[mik1996]

Not sure if this is helpful, but if you create an excel list you can just use the ReName_Files macro to rename, just make sure you include the path as part of the file name

So for example, if your excel has the below in it

Cell A1: C:\Users\Downloads\Test File.pdf
Cell B1: C:\Users\Downloads\This Is the same file.pdf

Your 'Test File.pdf' saved in your downloads folder would be renamed to 'This is the same file.pdf'

Sub ReName_Files()
Dim r As Integer
r = 1
Do Until IsEmpty(Cells(r, "A")) Or IsEmpty(Cells(r, "B"))
Name Cells(r, "A") As Cells(r, "B")
r = r + 1
Loop
MsgBox "All your old file names in Column 'A' have been reNamed" & vbCr & _
        "to the adjacent new name in column 'B'."
End Sub

Thats brilliant thank you, I will try this out!