1

I'm very new to VBA and have been messing around with a few codes for work. Essentially, I have a folder that contains 1000+ folders with different names--and I need to rename each folder. I have an excel sheet set up with the Original File Path, Old Folder name, and Desired Folder Name. I have found that this code works for the parent folder, but not the folders within it:

Sub rename_folder()
Dim old_name, new_name As String
For i = 2 To Sheets(1).Range("a1").End(xlDown).Row
new_name = Left(Sheets(1).Cells(i, 1).Value, Len(Sheets(1).Cells(i, 1).Value) - Len(Sheets(1).Cells(i, 2).Value))
new_name = new_name & Sheets(1).Cells(i, 3).Value
old_name = Sheets(1).Cells(i, 1).Value
Name old_name As new_name
Next i
End Sub

How do I get it so that this code renames all the folders within the parent folder? Any help would be much appreciated! Thanks.

Kazimierz Jawor
  • 18,861
  • 7
  • 35
  • 55
Nick Possi-Moses
  • 15
  • 1
  • 5
  • 1
    possible duplicate of [Cycle through sub-folders and files in a user-specified root directory](http://stackoverflow.com/questions/14245712/cycle-through-sub-folders-and-files-in-a-user-specified-root-directory) – RubberDuck Jun 17 '15 at 13:34

1 Answers1

1

There are 2 ways to go about this. The first (and much slower one) is to open each file with the old name, save as with the new name, and then move to the next.

I would reccomend the scripting style approach, using a file system object you can move files (rename them) within a loop.

Presuming the old and new file names have a relative path with them from some parent folder:

Dim fso As New FileSystemObject, ParentFolder as string
ParentFolder = "C:\Users\Me\ThisProject\"

For i = 2 To Sheets(1).Range("a1").End(xlDown).Row
    new_name = Left(Sheets(1).Cells(i, 1).Value, Len(Sheets(1).Cells(i, 1).Value) - Len(Sheets(1).Cells(i, 2).Value))
    new_name = new_name & Sheets(1).Cells(i, 3).Value
    old_name = Sheets(1).Cells(i, 1).Value

    'This will move (rename) the old file to the new one
    fso.MoveFile (ParentFolder & old_name), (ParentFolder & new_name)
Next i
Evan
  • 600
  • 2
  • 7
  • 34
  • 1
    If you want to define fso as a filesystemobject you will need to add a reference to your vba project to the Microsoft Scripting.Runtime reference. This reference also has a lot of other usefull objects like folder and file that you may be able to use. I prefer to define fso first (DIM fso as FileSystemObject) and then setting it to a new instance (SET fso = new FileSystemObject) and closing off the subroutine with destroying the fso object (SET fso = Nothing) This is a bit neater... but also no more than my preference. – Tom Jun 17 '15 at 13:47
  • Yes, forgot to add that to my answer but Tom is on point. You will need to add reference. [Here is a good answer on how to do that](http://stackoverflow.com/a/3236348/2497001) – Evan Jun 17 '15 at 13:50
  • Thanks for the help; so here is where I am out now. 1) For the code "For i = 2 To Sheets(1); I put in the name of the worksheet--would that still work? 2) Also, for code: Range("a1") .End(x1Down).Row -- Do i need to put a letter in that box to complete an array? – Nick Possi-Moses Jun 17 '15 at 14:39
  • Sheets("SheetName") works as well as indecies. Im not sure I follow the 2nd question. Range can be one cell so Range("A1") is legal. – Evan Jun 17 '15 at 14:54
  • When i try to run the macro I get this message: "Run-time error '5': invalid procedure call or arguement--it's referring to this line of code: "new_name = Left(Sheets(1).Cells(i, 1).Value, Len(Sheets(1).Cells(i, 1).Value) - Len(Sheets(1).Cells(i, 2).Value))" – Nick Possi-Moses Jun 17 '15 at 15:48
  • What does your spreadsheet data look like and what are you trying to accomplish with this line of code? – Evan Jun 17 '15 at 16:50
  • In Column A I put the file path for the subfolders, Column B I put the Old Folder Name, and in Column C I put the New Folder Name. Essentially, all I want to do is click a command button that executes a macro that renames all the folders in one file. – Nick Possi-Moses Jun 17 '15 at 17:10