VBA Batch File Renaming

[rachelml1130]

Hello,

I am attempting to batch rename files using the following macro created in VBA:

Sub RenameFile()
Dim z As String
Dim s As String
Dim V As Integer
Dim TotalRow As Integer

TotalRow = ActiveSheet.UsedRange.Rows.Count

For V = 1 To TotalRow

' Get value of each row in columns 1 start at row 2
z = Cells(V + 1, 1).Value
' Get value of each row in columns 2 start at row 2
s = Cells(V + 1, 2).Value

Dim sOldPathName As String
sOldPathName = z
'On Error Resume Next
Name sOldPathName As s

Next V

MsgBox "Congratulations! You have successfully renamed all the files"

End Sub

I keep getting an error when I run it though on the underlined line. But the naming shows as correct. I have tried a different but similar one as well and still have the same error on the same line. Does anyone know what I might have wrong here?

 

[Momentman]

Welcome to the forum

I think I do (what might be wrong here?)

change

 For V = 1 to Totalrow

to

For V = 1 to Totalrow-1

and you should be fine

Because in your code, you use cells(V+1,1), so by the rime V=Totalrow, say 10 for example, V+1 will be 11 and row 11 is a blank row which makes the Name statement return and error 'cos the Name statement sees Name "blank" as "blank", something like that

or make your code this way

Sub RenameFile()
    Dim z As String
    Dim s As String
    Dim V As Integer
    Dim TotalRow As Integer
    
    TotalRow = ActiveSheet.UsedRange.Rows.Count
    
    For V = 2 To TotalRow
    
        ' Get value of each row in columns 1 start at row 2
        z = Cells(V, 1).Value
        ' Get value of each row in columns 2 start at row 2
        s = Cells(V, 2).Value
    
        Dim sOldPathName As String
        sOldPathName = z
        'On Error Resume Next
        Name sOldPathName As s
        
    Next V
    
    MsgBox "Congratulations! You have successfully renamed all the files"
    
End Sub