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

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

excelvbaperformanceautomationoffice365


Solution 1:[1]

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

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 ricardogerbaudo
Solution 2

Related Questions