Need to add Suffix in specific file name

[Aswinraj]

Hello All,

I have a Macro, where it will open Previous file and Refresh the data. After Refresh it will Save the file.
Now I want to add "_Refreshed" in the File name without changing existing file name.

Example: File Name Before Macro Refresh "Markets_ 29 May'20.xlsb" and If i am doing refresh today then it should save as "Markets_29May'20_Refreshed.xlsb"

Can anyone help please.

 

[Dan_W]

I don't know if this is exactly what you wanted, but it does work:

Sub SaveRefreshed(wbTarget As Workbook)

    Dim strFilename         As String
    Dim strNewFilename      As String
    Dim strExtension        As String
    Dim strTemp             As String
    Dim lngTemp             As Long
    
    strFilename = wbTarget.FullName
    lngTemp = InStrRev(strFilename, ".")
    strTemp = Left(strFilename, lngTemp - 1) & "_Refreshed."
    strExtension = Right(strFilename, Len(strFilename) - lngTemp)
    
    strNewFilename = strTemp & strExtension
    wbTarget.SaveAs strNewFilename
   ' If you want to save a copy, then replace the line with:
    'wbTarget.SaveCopyAs strNewFilename
         
End Sub

It takes a Workbook as an argument, and then adds the suffix. In the code above, it will save that workbook with the new filename. If, however, you want to save a copy of the workbook, then you just need to replace the code with the one in the comments.

Example usage:

SaveRefreshed Thisworkbook SaveRefreshed Application.Activeworkbook SaveRefreshed Application.Workbooks("Markets_ 29 May20.xlsb")

 

[Aswinraj]

@Dan_W Thanks for your code but i am unable to call this code. May i know how to incorporate this code into any existing code.

 

[Aswinraj]

@Dan_W getting error in wbTarget

 

[Dan_W]

How are you calling the subroutine?