Use VBA Code to change folder names based on excel List Column

user_19019717 0
Viewed 79

I have folders showing up as the following Folder image

I have the path of these folders in Cell B1 and the Name I need to change the folders in Column A. I do not have that much VBA experience and cannot get how to get folders name to change to the listing on the listing I have on Column A. Note: I'm only showing four tasks in the image, but I have over 50 for multiple folders and I have the name of what the file should be in excel sheets, which is why I need this to automate. I wouldn't even care if i could at least get the parentheses to be replaced by the names I have on Column A of my excel sheet.

Thanks for any help!! IMAGE HERE

office365 vba excel automation performance
2 Answers

please take a look at this solution:

Sub RenameFolders()
    
    Dim lastRow As Integer
    lastRow = WorksheetFunction.CountA(Range("A:A"))
    
    Dim newFolderName As String
    Dim oldFolderName As String
    
    Dim newFolderRange As Range
    
    For Each newFolderRange In Range("A1:A" & lastRow)
        oldFolderName = newFolderRange.Offset(0, 1).Value
        newFolderName = GetParentPath(oldFolderName) & newFolderRange.Value
        Name oldFolderName As newFolderName
    Next newFolderRange

End Sub

Function GetParentPath(ByVal path As String)
    
    path = StrReverse(path)
    GetParentPath = StrReverse(Mid(path, InStr(1, path, "\")))

End Function
user_70111290

Using PowerRename + VBA

To avoid having to handle RegEx or string matching in VBA, we can handle that quickly using PowerRename with by matching the regular expression Task (\d+) .* and replacing with the captured task number using $1, as shown below

PowerRename

This leaves us with a much simpler renaming task that can be handled with a single line line of VBA. Specifically, if you drop the path to the directory (including the terminal \) into cell C1 then you can use the immediate window command

For Row=1 To [CountA(A:A)]:Name [C1]&Row As [C1]&Cells(Row,1):Next

to rename the files to your existing list. You can access the immediate window with Alt + F11 then Ctrl + G. The end result should look something like the below:

Renaming

Note that I am using the command

For Each fld in CreateObject("Scripting.FileSystemObject").GetFolder([C1]).SubFolders: ?fld.Name: Next

to verify the change in the image above

user_70999060
Related