set excel <filename to <filename-date
 

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...

set excel <filename to <filename-date
 

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

"bob engler" wrote in message
...
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...

set excel <filename to <filename-date
 

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

bob engler schrieb:

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...