Want to add suffix before file name extension

[Samadhan Gaikwad]

I have below code that adds listed suffix and prefix to file names listed in "B" column. But problem is, it adds suffix after file extension. I wants to add text at the end of file names.

Sub Add_Pre_Suf()
    Dim Pre, Suf As String
    Dim r As Range
    Pre = Range("C2").Value
    Suf = Range("D2").Value
    Range("B2").Select
    'Range(Selection, Selection.End(xlDown)).Select
    Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).Select
    With Selection
       
        For Each r In Selection
            r.Value = Pre & r.Value & Suf
        Next
       
    End With
    RenameFiles
End Sub

 

[vletm]

@Samadhan Gaikwad
You think like: Pre & Suf & r.Value
The code do like: Pre & r.Value & Suf
> ask code to do as You think <

 

[Samadhan Gaikwad]

@vletm
Thank you for the reply.
But if my file name is test.txt and want to add prefix and suffix 1 and 9 respectively.
As per your suggestion, it renames as "19test.txt" and I want "1test9.txt"

 

[jindon]

If the selected cell(s) contain extension then try

Sub Add_Pre_Suf()
    Dim Pre, Suf As String
    Dim r As Range, fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Pre = Range("C2").Value
    Suf = Range("D2").Value
    Range("B2").Select
    'Range(Selection, Selection.End(xlDown)).Select
   Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).Select
    With Selection
      
        For Each r In Selection
            r.Value = Pre & fso.GetBaseName(r.Value) & Suf & "." & fso.GetExtensionName(r.Value)
        Next
      
    End With
    RenameFiles
End Sub

 

[vletm]

@Samadhan Gaikwad
... if You 'just' tell like 'r.Value = Pre & r.Value & Suf'
and You had FILENAME (not necessary any extension) then
how ... without enough details?
I hope after digging, previous reply's code works!

 

[Samadhan Gaikwad]

@Samadhan Gaikwad
... if You 'just' tell like 'r.Value = Pre & r.Value & Suf'
and You had FILENAME (not necessary any extension) then
how ... without enough details?
I hope after digging, previous reply's code works!

Ok. My mistake.

 

[Samadhan Gaikwad]

If the selected cell(s) contain extension then try

Sub Add_Pre_Suf()
    Dim Pre, Suf As String
    Dim r As Range, fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Pre = Range("C2").Value
    Suf = Range("D2").Value
    Range("B2").Select
    'Range(Selection, Selection.End(xlDown)).Select
   Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).Select
    With Selection
     
        For Each r In Selection
            r.Value = Pre & fso.GetBaseName(r.Value) & Suf & "." & fso.GetExtensionName(r.Value)
        Next
     
    End With
    RenameFiles
End Sub

Nice, it's just what I wanted.