Solved Rename Folders in Bulk (1 Viewer)

[jack555]

Have to rename the folders in a location. New name as per data in a table below.

Sample Data

Old Name	New Name
1234	ABCD
2345	EFGH
5678	SCHO
8937	MIBZ

I managed to get the code for renaming the folder, however, this works for one folder only. I want to rename the folders as above in one go. Please assist.

'VBA Rename a Folder
Sub VBAF1_Rename_Folder()
    
    'Variable declaration
    Dim sFolder_OldName As String
    Dim sFolder_NewName As String
    
    'Existing Folder Name
    sFolder_OldName = "C:\VBAF1\Sample\"
    
    'Define New Name of folder to change
    sFolder_NewName = "C:\VBAF1\Test\"
    
    'Check Specified folder exists or not
     If Dir(sFolder_OldName) <> "" Then
        'Rename Folder using Name function
        Name sFolder_OldName As sFolder_NewName
        MsgBox "Folder has renamed.", vbInformation, "VBAF1"
    Else
        MsgBox "Folder is not available to rename.", vbInformation, "VBAF1"
    End If
        
End Sub

 

[Ranman256]

set rst = currentdb.openrecordset("select * from tbl")
with rst
   while not .eof
      vOldName = .fields("oldName").value
      vNewName = .fields("NewName").value

   'run your rename code here

    .movenext    'get next record
  wend 
end with

 

[theDBguy]

How do you know what the old name was and what the new name needs to be? If you store them in a table, this would be possible using a loop.

Edit: Oops, too late...

 

[jack555]

set rst = currentdb.openrecordset("select * from tbl")
with rst
   while not .eof
      vOldName = .fields("oldName").value
      vNewName = .fields("NewName").value

   'run your rename code here

    .movenext    'get next record
  wend
end with

Thanks for the instant reply. I used the code as below in a Form command button. However, no changes were made. Am I doing anything differently?

Private Sub Command36_Click()

'Variable declaration
    Dim sFolder_OldName As String
    Dim sFolder_NewName As String

set rst = currentdb.openrecordset("select * from tblNameChangeSource")
with rst
   while not .eof
      vOldName = .fields("oldName").value
      vNewName = .fields("NewName").value

   'run your rename code here
    'Existing Folder Name
    sFolder_OldName = "C:\VBAF1\vOldName\"
    
    'Define New Name of folder to change
    sFolder_NewName = "C:\VBAF1\vNewName\"
    
    'Check Specified folder exists or not
     If Dir(sFolder_OldName) <> "" Then
        'Rename Folder using Name function
        Name sFolder_OldName As sFolder_NewName
        MsgBox "Folder has renamed.", vbInformation, "VBAF1"
    Else
       ' MsgBox "Folder is not available to rename.", vbInformation, "VBAF1"
    End If

    .movenext    'get next record
  wend
end with
End Sub

 

[Pat Hartman]

You never answered the question regarding how the code is supposed to know how to change the file names. These things don't happen by magic.

 

[jack555]

You never answered the question regarding how the code is supposed to know how to change the file names. These things don't happen by magic.

the names to be changed in the given table below. Already included in my first question itself.

tblNameChangeSource

Old Name	New Name
1234	ABCD
2345	EFGH
5678	SCHO
8937	MIBZ

 

[Pat Hartman]

So you have FOUR files and you want them changed to the other four names. Why would you bother with code to do this? Here it is

Name "1234" As "ABCD"
Name "2345" As "EFGH"
Name "5678" As "SCHO"
Name "8937" As "MIBZ"

 

[jack555]

So you have FOUR files and you want them changed to the other four names. Why would you bother with code to do this? Here it is

Name "1234" As "ABCD"
Name "2345" As "EFGH"
Name "5678" As "SCHO"
Name "8937" As "MIBZ"

These 4 are samples to understand the scenario. I have more than 2000 folders to rename in a short time.

 

[Pat Hartman]

And we asked you HOW DO YOU GET FROM A TO B and you won't tell us. We can't make it up. ONLY YOU know the rule. You have code that loops through a folder. You have the "Name" command to do the rename. Write 2000 lines of code to hard code the names or come up with a formula to figure out how to get from a to b. You've given us nothing to work with.

 

[CJ_London]

@Pat Hartman OP has told us in post #4 - oldname and new name are stored in a table. problem is their code is not working as required.

Looks like they got the code from here

VBA Rename Folder or Directory | Explained with Examples | VBAF1

VBA Rename Folder or Directory to rename a folder to change existing name. In this tutorial we explained examples with instructions.

vbaf1.com

but have not adapted it to meet their actual requirement which looks like it needs to be

'Existing Folder Name
    sFolder_OldName = "C:\VBAF1\vOldName\"
   
    'Define New Name of folder to change
    sFolder_NewName = "C:\VBAF1\vNewName\"

needs changing to

'Existing Folder Name
    sFolder_OldName = "C:\VBAF1\" & rst!OldName & "\"
   
    'Define New Name of folder to change
    sFolder_NewName = "C:\VBAF1\" & rst!NewName & "\"

although since the example code in the link uses a parent folder of 'C:\VBAF1\' I suspect this bit needs changing as well

 

[Pat Hartman]

He never answered the question about where the data was coming from. But I see now that I read the code, there is a table. I'll just go away and leave you to it

 

[jack555]

& rst!OldName & "\"

This solved the problem. Correct usage of delimiter in the earlier given code solved it. Thanks to Ranman256 and CJ_London