VBA rename by adding a space

Question

I want to add a space in between a document name. However the code can't pass through the IF True line.

Document name having random name as below:

INV500TAT1234 Rev 1 Form_Health.pdf

Wanted it to be:

INV500 TAT1234 Rev 1 Form_Health.pdf #with a space in between 500 & TAT

Note that some of the document does already contain INV500 TAT, so I wrote the if to skip them

Sub Rename_Space()
    Dim ffile As Variant, path As String
    path = "C:\Users\me\Desktop\Rename\"
    ffile = Dir(path)
    While ffile <> ""
        If ffile = "INV500TAT*" Then
            old_name = path & ffile
            new_name = path & "INV500 TAT*"
            Name old_name As new_name
        End If
        ffile = Dir
    Wend
End Sub

user_9156979 · Answer

That will make a space in every filename which starts with INV500TAT between the 500 and TAT.

Sub Rename_Space()
    Dim ffile As Variant, path As String
    path = "C:\Users\me\Desktop\Rename\"
    ffile = Dir(path)
    While ffile <> ""
    Debug.Print ffile
        If Left(ffile, 9) = "INV500TAT" Then
        old_name = path & ffile
        new_name = path & Left(ffile, 6) & " " & Mid(ffile, 7, Len(ffile))
        Name old_name As new_name
        End If
        ffile = Dir
    Wend
End Sub

VBA rename by adding a space

1 Answers