Save File as the "FileSelectedtoOpenInMacro(Extract)"

[thumperjlp]

When run, my macro prompts the user to select a file to copy, which it then copies the specified sheet & pastes to a new sheet. The selected file will then close and the macro then formats, deletes, etc. the data in the copied sheet.
I then want to save this new formatted file as the name of the file which the user selected at the beginning of the macro and add "extract" to the end of the file name. i.e. userselectedfile(extract).xls so as not to overwrite the original file.
I've searched high and low for a solution with no luck. Any help would be greatly appreciated.

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Below is some code you could try.

Sub AppendName_SaveAs()
    Dim sNewName As String, sExt As String
    Const sAppend As String = "(extract)"

    With ActiveWorkbook
        sExt = "." & Split(.Name, ".")(UBound(Split(.Name, ".")))
        sNewName = Left(.Name, Len(.Name) - Len(sExt)) _
            & sAppend & sExt
        .SaveAs Filename:=.Path & "\" & sNewName
    End With

End Sub

 

[Biz]

Hi Jerry,

Nice Script. Another alternative script which includes File naming like an Accountant lol.

Sub AppendName_SaveAsBKFix()
    Dim sNewName As String, sExt As String
    Const sAppend As String = "(extract)"
    With ActiveWorkbook
        sExt = "." & Split(.Name, ".")(UBound(Split(.Name, ".")))
        sNewName = Left(.Name, Len(.Name) - Len(sExt)) _
            & sAppend & " " & "as at " & Format(Now, "dd-mmm-yy h-mm-ss") & sExt
        .SaveAs Filename:=.Path & "\" & sNewName
    End With
End Sub

Biz

 

[thumperjlp]

Great; both worked perfectly. Is there a way to delete the macros in the new(copied) sheet before saving it so the user isn't prompted to save work when trying to close the extract? It seems as though this wouldn't be possible because the macro is needed to save the file, but if anyone has a solution that would be awesome.

 

[Jerry Sullivan]

Great; both worked perfectly. Is there a way to delete the macros in the new(copied) sheet before saving it so the user isn't prompted to save work when trying to close the extract? It seems as though this wouldn't be possible because the macro is needed to save the file, but if anyone has a solution that would be awesome.

If you are using xl2007 or later, you can save to an .xlsx format. This doesn't immediately delete the macro, but if you close then reopen the file the macros will be gone.

Sub AppendName_SaveAsBKFix()
    Dim sNewName As String, sExt As String
    Const sAppend As String = "(extract)"
    With ActiveWorkbook
        sExt = "." & Split(.Name, ".")(UBound(Split(.Name, ".")))
        sNewName = Left(.Name, Len(.Name) - Len(sExt)) _
            & sAppend & " " & "as at " & Format(Now, "dd-mmm-yy h-mm-ss") & ".xlsx"
        Application.DisplayAlerts = False 'Optional -Hide alert that macros won't be saved.
        .SaveAs Filename:=.Path & "\" & sNewName, FileFormat:=xlOpenXMLWorkbook
        Application.DisplayAlerts = True
    End With
End Sub

Altenatively, if this is a function your users do often, you might want to place it in their Personal.xlsb file or in an add-in file.