Rename Files in Folder using VBA

[Poppaby]

Hi all

I have a folder with a large number of files. I have listed them all in a column OldName. I want to rename them to the name in column NewName. The directory is listed at the top

Any help would be appreciated


Additionally, each file has a single sheet called Sheet1. I would like to add a column after the last column containing data, populated with the new file name as the column header. So the file listed in row 4 of the picture above would have a new column added with 20001Allowances01 as the header

Dave

 

[offthelip]

You have really got two entirely different questions here. this code should rename your files.

Sub rename()
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(LastRow, 10))
pathn = inarr(1, 1)
For i = 3 To LastRow
'You can change the path and file name
    src = pathn & "\" & inarr(i, 1)
    dst = pathn & "\" & inarr(i, 1)
     Name src As dst
Next i
End Sub

 

[offthelip]

whoops an error try this:

Sub rename()
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(LastRow, 10))
pathn = inarr(1, 1)
For i = 3 To LastRow
'You can change the path and file name
    src = pathn & "\" & inarr(i, 1)
    dst = pathn & "\" & inarr(i, 2)
     Name src As dst
Next i
End Sub

 

[Poppaby]

Thanks - I am getting an error message saying file not found

 

[offthelip]

When you get the error go into debug and hover your mouse over the "src" variable and see what file it is showing this is the file that it can't find.

 

[Marc L]

Hi,​

​

should obviously be :​

For i = 4 To LastRow