set excel <filename> to <filename-date>

[bob engler]

I would like to have the filename of a excel WB set to
<filename-currentdate> on open the original file so when
the people save it, it will save it to the new name. ie:

open MGA (template file) and make the active WB
MGA-071106 would be a copy of MGA.xls. That way when
the people click Save, it will save it to the correct new file (they
have problems following simple instructions).

Thanks...

 

[NickHK]

Bob,
There is the Workbook_BeforeSave event. This will update the file name each
day, if it is subsequently saved on a later day, which may not be what you
want, but ...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim strFileName As String
strFileName = "MGA-" & Format(Date, "mmddyy") & ".xls"
Application.EnableEvents = False
With ThisWorkbook
If .Name = strFileName Then
.Save
Else
.SaveAs "MGA-" & Format(Date, "mmddyy") & ".xls"
End If
End With
Application.EnableEvents = True
Cancel = True
End Sub

You may also need to deal with the situation if there is already a file with
this name present.

NickHK

 

[Ingolf]

Hi Bob,

copy the following code to the module of your workbook. This will add
the date to your workbook name and save the workbook on opening it.

Private Sub Workbook_Open()
ThisWorkbook.SaveAs Filename:=Left(ThisWorkbook.FullName, _
Len(ThisWorkbook.FullName) - 4) & "-" & Format(Date, "mmddyy") & ".xls"
End Sub

Regards,
Ingolf