CODEWITHSUNDEEP
vbaexcel
DoYouLoveMe
DoYouLoveMe

Reputation: 447

Auto generated save excel file name VBA Macro?

Auto generated excel file name VBA Macro? Hi all i want auto generated excel file name in macro my code is below here

Sub Sheet_SaveAs()
  Dim wb As Workbook
  Sheets("Sheet1").Copy
  Set wb = ActiveWorkbook
  With wb
    .SaveAs ThisWorkbook.Path & "\autogenrate.xlsx"
    '.Close False
  End With
End Sub

my code is working fine but when i save next time then asking do you want replace it but i want auto generate name

Upvotes: 1

Views: 5266

Answers (1)

stucharo
stucharo

Reputation: 855

The simplest fix is to change to a unique name each time. The easiest way to do this might be to use a date-time string

Sub Sheet_SaveAs()
  Dim wb As Workbook
  Sheets("Sheet1").Copy
  Set wb = ActiveWorkbook
  With wb
    .SaveAs ThisWorkbook.Path & "\" & _
            Format(Now, "yyyymmdd") & _
            Replace(Format(Now, "Long Time"), ":", "") & _
            ".xlsx"
    .Close False
  End With
End Sub

The date and tie part are seperate to allow you to use seconds and therefore your limit is 1 save per second. If you need more frequent saves you would have to include a millisecond counter too. The good thing about this method is that it keeps your backups in sequential order in the folder.

You can read more about formatting dates etc. here - https://msdn.microsoft.com/en-us/library/office/gg251755.aspx

Upvotes: 1

Related Questions