Add suffix to existing filename

[delaing]

I am needing code to save an existing workbook while adding a suffix to the existing filename.

i.e. "workbook.xls" becomes "workbook reviewed.xls"

Also, if the routine can do it regardless of the existing extension; may be xls, xlsx, or xlsm, that would be helpful.

Thank you,
Delain

[Kenneth Hobson]

This should get you nearly there. Concatenate the strings to build your filename and replace it and the file type from a recorded File > SaveAs macro.

Sub ken()  
  Dim fn As String
  fn = ThisWorkbook.FullName
  Debug.Print fn, ThisWorkbook.Path
  Debug.Print GetBaseName(fn), GetFileExt(fn)
  Debug.Print ThisWorkbook.FileFormat
End Sub

Function GetBaseName(filespec As String)
  Dim FSO As Object
  Set FSO = CreateObject("Scripting.FileSystemObject")
  GetBaseName = FSO.GetBaseName(filespec)
End Function

Function GetFileExt(filespec As String)
  Dim FSO As Object, s As String
  Set FSO = CreateObject("Scripting.FileSystemObject")
  s = FSO.GetExtensionName(filespec)
  Set FSO = Nothing
  GetFileExt = s
End Function

[jolivanes]

Another way if it is for the workbook you are in.
Saves the workbook in the same folder with the new name.

Sub jolivanes()
    Dim a As String, aa As String
    a = Trim(Split(ActiveWorkbook.Name, ".")(0))
    aa = Trim(Split(ActiveWorkbook.Name, ".")(1))
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & a & " Reviewed." & aa
    'ActiveWorkbook.Close    '<----- ??????	
End Sub