CODEWITHSUNDEEP
excelvba
Glacestorm
Glacestorm

Reputation: 1

Renaming Sub-Folders in a Folder using Excel VBA

I'm trying to rename all the sub-folders in a specific folder (C:Users\admin\Documents\AWS). In Column A I have the old folder names, to be changed to the corresponding new names in B.

This is the code I tried (typed in Sheet17 in Visual Basic Editor). Sheet17 is the spreadsheet in the workbook with the two columns of text. All the sub-folders are in the AWS folder.

Sub rename_folder()
    Const FILEPATH As String = "C:Users\admin\Documents\AWS\"
    Dim strfile As String
    Dim filenum As String
    strfile = Dir(FILEPATH)

    Dim old_name, new_name As String

    For i = 2 To Sheets(17).Range("a1").End(xlDown).Row
        strOldDirName = FILEPATH & Sheets(17).Cells(i, 1).Value
        strNewDirName = FILEPATH & Sheets(17).Cells(i, 2).Value
        Name strOldDirName As strNewDirName
    Next i

    MsgBox "Congratulations! You have successfully renamed all the Folders"
End Sub

I got

Run-time error '53': File not found

when running the code. Couldn't find the Debug Window (I'm on Mac, using Microsoft 2017), but it's probably one of the first few lines of code. Please let me know if any of the subsequent lines should be changed as well.

Upvotes: 0

Views: 2221

Answers (2)

ASH
ASH

Reputation: 20322

This worked fine for me.

Sub rename_folder()
Const FILEPATH As String = "C:\Users\Excel\Desktop\test\"
Dim strfile As String
Dim filenum As String
strfile = Dir(FILEPATH)

Dim old_name, new_name As String
For i = 2 To Sheets(1).Range("a2").End(xlDown).Row
    strOldDirName = FILEPATH & Sheets(1).Cells(i, 1).Value
    strNewDirName = FILEPATH & Sheets(1).Cells(i, 2).Value
    Name strOldDirName As strNewDirName
Next i
MsgBox "Congratulations! You have successfully renamed all the Folders"
End Sub

Before:

enter image description here

After:

enter image description here

Upvotes: 1

DeBugOfen
DeBugOfen

Reputation: 146

Could be

Const FILEPATH As String = "C:\Users\admin\Documents\AWS\"

instead of

Const FILEPATH As String = "C:Users\admin\Documents\AWS\"

cause of the missing \ after drive letter

Upvotes: 0

Related Questions