CODEWITHSUNDEEP
vbaexcel
mits
mits

Reputation: 926

VBA excel - How to 'saveas' file without preserving the first file

I have a workbook named sth_A where A is for today's date in dd-mm-yyyy format. Almost every day I open this workbook and make changes and then I update the file's name with today's date (A).

So I created a Workbook_BeforeClose event with intention to write some code in it, that would rename my file automatically everytime I close the file. I used Workbook.SaveAs method, but this preserves the old file also.

Is there a way to rename an open file, or saveas without preserving the original, or another aproach that would do what I want in an elegant way?

code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.SaveAs "sth" & Format(Date, "dd-mm-yyyy") & ".xlsm", FileFormat:=52, CreateBackup:=False
End Sub

also:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.SaveAs ThisWorkbook.path & "/sth" & Format(Date, "dd-mm-yyyy") & ".xlsm", FileFormat:=52, CreateBackup:=False
End Sub

P.S. I want the first file deleted/overwriten in any case. Not only if it has the same name with the new one.

Upvotes: 0

Views: 165

Answers (3)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19857

Save your workbook and Kill the old one (basically what @Canute said).

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim sOldName As String

    sOldName = ThisWorkbook.FullName

    ThisWorkbook.SaveAs ThisWorkbook.Path & Application.PathSeparator & _
        "sth" & Format(Date, "dd-mm-yyyy") & ".xlsm", FileFormat:=52, CreateBackup:=False

    Kill sOldName

End Sub

Upvotes: 1

Tony
Tony

Reputation: 69

Add disable alert.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
ThisWorkbook.SaveAs "sth" & Format(Date, "dd-mm-yyyy") & ".xlsm"
End Sub

Upvotes: 0

Canute
Canute

Reputation: 69

If you want to do this in the close workbook event then,

  1. First,save the open workbooks full name in a variable.(say oldWorkbook)
  2. Do a save-as on the open workbook and append your date time stamp.(this is your new workbook)
  3. Delete the workbook in which you had previously stored the path in variable oldWorkbook.

Hope this helps.

Upvotes: 0

Related Questions