Rename folders using values from sheet

**cjconnor24** · 04-30-2009, 04:35 AM

Hi all,

I have a list of reference numbers in column A, and an address in column B.

Along with that - i have a folder which contains loads of folders named with the reference numbers. Is there a way using VBA, that i can go through each cell in column A and perform the following:

Go through each reference number in column A, check if there is a folder with that name, if so - rename with the corresponding value in column B.

I hope i have explained this correctly

**antoka05** · 04-30-2009, 04:39 AM

You can try with this code:

Sub Macro1()
   Dim objFSO, fso
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set fso = objFSO.GetFolder("c:\myFolder")  'path to rename
   fso.Move ("c:\" & Range("a1"))             'rename path with name from Range  a1

End Sub

Regards,
Antonio

**cjconnor24** · 04-30-2009, 04:41 AM

Hi,

I should have said that i have around 300 rows so i would need to perform this for each row. Any ideas?

Thanks again!

**antoka05** · 04-30-2009, 04:51 AM

I apologize for my mistake.

Try with this code:

Sub Macro1()
   Dim objFSO, fso
   Dim lastRow As Long, r As Long
   Dim fromFolder As String, toFolder As String
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   
   lastRow = Cells(Rows.Count, "a").End(xlUp).Row
   
   For r = 1 To lastRow
      fromFolder = Cells(r, "a")
      toFolder = Cells(r, "b")
      If objFSO.folderExists(fromFolder) Then
         Set fso = objFSO.GetFolder(fromFolder)
         fso.Move (toFolder)
      End If
   Next r
End Sub

Regards,
Antonio