VBA Renaming Electronic Files

[OilEconomist]

Thanks in advance for any suggestions which I will provide feedback.

I am making an Excel file where I place the directory path (Cell B3) of where files that I may want to rename are located.

It first obtains the file names and places them in Column A starting with Row 5.

I then place the new names in Column B starting in Row 5.

I get the error when I try to run the Macro to rename "Run Time Error 53: File not Found"

Name DirFolderRename & CurrentName As DirFolderRename & NewName

The following is the entire code to rename the files:

Sub RenameFile()


'Dimensioning
    Dim DirFolderRename As String
    Dim CurrentName As String
    Dim NewName As String
    Dim i As Integer
    
    i = 5
    
    DirFolderRename = Sheets("List").Range("B3").Value
    
    Do While Sheets("List").Cells(i, 1).Text <> "" And Sheets("List").Cells(i, 2).Text <> ""
    
        CurrentName = Sheets("List").Cells(i, 1).Text <> ""
        NewName = Sheets("List").Cells(i, 2).Text <> ""
        Name DirFolderRename & CurrentName As DirFolderRename & NewName
        
        i = 1 + 1
        
    Loop
    
    MsgBox ("Complete")
    
    
End Sub

 

[Fluff]

Both these lines will give you a value of true or false, depending on if the cell is empty

        CurrentName = Sheets("List").Cells(i, 1).Text <> ""
        NewName = Sheets("List").Cells(i, 2).Text <> ""

Remove this part from both of them

<> ""

 

[OilEconomist]

Thanks so much Fluff! That worked, but only the first file get renames. It executes on line 5 where Cell(5, 1) has the old file name and Cell(5, 2) has the new file name and then says complete.

Any idea why it's not looping through?

 

[Fluff]

This

 i = 1 + 1

should be

 i = [COLOR=#ff0000]i[/COLOR] + 1

 

[OilEconomist]

Thanks so much Fluff and apologies for the oversight.

 

[Fluff]

You're welcome & thanks for the feedback

 

[OilEconomist]

I appologize.

This is strange, because it worked for a second and then now I'm getting the same error. Any idea why?

"Run-time error '53': File Not Found"

 Name DirFolderRename & CurrentName As DirFolderRename & NewName

Full Code

Sub RenameFile()


'Dimensioning
    Dim DirFolderRename As String
    Dim CurrentName As String
    Dim NewName As String
    Dim i As Integer
    
    i = 5
    
    DirFolderRename = Sheets("List").Range("B3").Value
     
    Do While Sheets("List").Cells(i, 1).Text <> "" And Sheets("List").Cells(i, 2).Text <> ""
    
        CurrentName = Sheets("List").Cells(i, 1).Text
        NewName = Sheets("List").Cells(i, 2).Text
        Name DirFolderRename & CurrentName As DirFolderRename & NewName
        
        i = i + 1
        
    Loop
    
    MsgBox ("Complete")
    
    
End Sub

 

[Fluff]

If you've already run the macro, then the files in col A will no longer exists.

 

[OilEconomist]

True, but I was running it on a different directory where the file names did not change.

 

[Fluff]

In that case check the values of CurrentName & DirFolderRename when you get the error. Then check that the file exists