Insert text into filename

[davegb]

I have a folder full of files that I open, modify and then save to
another folder, all done by a macro. I'd like the macro to rename the
files, taking the old name and adding Mod to the name. So if the old
file is "xyz.xls" I'd like it to be "xyz Mod.xls".
I appreciate your help.

 

[Guest]

Sub test()
Dim SaveName As String
SaveName = SaveMod(ThisWorkbook) ' Change the 'Thisworkbook' to whatever
workbook you want
End Sub


Public Function SaveMod(Workbook As Workbook) As String
Dim strName As String, strExt As String, strpath
With Workbook
strName = .Name
strpath = .Path
If Right(strpath, 1) <> "\" Then
strpath = strpath & "\"
End If
strExt = Right(strName, 4)
strName = Left(strName, Len(strName) - 4)
strName = strName & "Mod" & strExt
SaveMod = strpath & strName
.SaveCopyAs SaveMod
End With
End Function

 

[davegb]

Thanks!

Sub test()
Dim SaveName As String
SaveName = SaveMod(ThisWorkbook) ' Change the 'Thisworkbook' to whatever
workbook you want
End Sub


Public Function SaveMod(Workbook As Workbook) As String
Dim strName As String, strExt As String, strpath
With Workbook
strName = .Name
strpath = .Path
If Right(strpath, 1) <> "\" Then
strpath = strpath & "\"
End If
strExt = Right(strName, 4)
strName = Left(strName, Len(strName) - 4)
strName = strName & "Mod" & strExt
SaveMod = strpath & strName
.SaveCopyAs SaveMod
End With
End Function