VBA Code to Modify String by Changing the last word between the second to the last backslash and then last one

[OilEconomist]

Thanks in advance for any help given!

What is the VBA Code to take an existing string and modify the last word. I would like to change e-mails to attachments.

For example, I have the directory stored:
C:\Files\Section A\e-mails\

and I would like to modify it to:
C:\Files\Section A\Attachments\

Option Explicit

Sub Modify_String()

Dim String_Org As String, String_New As String


String_Org = "C:\Files\Section A\e-mails\"

'Code to change String_Org to "C:\Files\Section A\Attachments\" and store it as String_Mod
    
    'String_Mod = ?

End Sub

 

[Skyybot]

The phrasing of your question confuses me, but to change the last "word" of a string requires either length or delimiter. It looks like, in your case, the string will be a file location so "\" will be the delimiter. Here is a generic code block to change the last "word" of a string. Modify it to suit your needs.

Option Explicit

Sub ChangeLastWord()

Dim base_string As String, change_last As String, new_string As String

base_string = "C:\Users\User1\Documents"

change_last = "Pictures"

new_string = Left(base_string, Len(base_string) - InStr(1, base_string, "User1\")) & "\" & change_last

End Sub

 

[kvsrinivasamurthy]

Try.

String_Org = "C:\Files\Section A\e-mails\"
String_Mod = Replace(String_Org, "e-mails", "Attachments")