VBA Batch File Renaming

rachelml1130

New Member
Joined
Jun 16, 2016
Messages
2
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?
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Welcome to the forum

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

change
Code:
 For V = 1 to Totalrow
to
Code:
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,621
Members
412,333
Latest member
hectamuptra
Top