excel - My VBA to change a folder name isn't quite right, what am I missing?

Question

I'm trying to put in some VBA that checks if a folder name should be changed based on whether something in the record has been changed (i.e. the folder was initially opened with a spelling mistake or similar).

The current name of the folder is fixed in cell B51 on a background sheet as soon as a user opens the form to update anything (and before they actually do any updating). A formula in B52 shows how the folder would be named based on whatever they've updated. So, if the two differ, it means the folder name needs to be changed to the new one.

Here's what i have;

Dim strOldDirName As String
Dim strNewDirName As String

strOldDirName = Sheets("Inputs").Range("B51").Value
strNewDirName = Sheets("Inputs").Range("B52").Value

If strOldDirName <> strNewDirName Then
Name strOldDirName As strNewDirName
Else
End If

All of the updates that the user makes are stored in some other code and it all works great, apart from the folder name is not changing.

strOldDirName and strNewDirName are full file paths including folder name. I'm wondering if the folder name need to be defined separately to the file path - Could that be why it's not updating the folder name or am I missing something else?

EDIT - here is screenshot of the old and new paths - the change being that a persons name has been added to the folder name instead of it being '0' (picture added to redact company name from the path!)

question from:https://stackoverflow.com/questions/65842038/my-vba-to-change-a-folder-name-isnt-quite-right-what-am-i-missing

Answer 1

Since we're not sure about what is the problem. Maybe have a go using FileSystemObject to rename rather than the Name function.

This should grab the last folder name (by searching for the last symbol in the path and extracting what's right of it. See if this works.

Edit: I forgot about the check. I would put it before changing the strNewDirName variable.

Dim strOldDirName As String
Dim strNewDirName As String

strOldDirName = Sheets("Inputs").Range("B51").Value
strNewDirName = Sheets("Inputs").Range("B52").Value

If strOldDirName <> strNewDirName Then
    strNewDirName = Right(strNewDirName, Len(strNewDirName) - InStrRev(strNewDirName, ""))

    Dim FSO As Object, FSOFolder As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FSOFolder = FSO.GetFolder(strOldDirName)

    FSOFolder.Name = strNewDirName

End If