file save as

[Phil Wales]

I want to save a file on a macro button so it saves the file as the same name
but gives the saved date. The file will be in the location each time and
will build day on day.

 

[Dave Peterson]

This may get you started:

Option Explicit
Sub testme()

Dim myFileName As String
Dim myExt As String

myFileName = ThisWorkbook.Name

'find extension
myExt = Mid(myFileName, InStrRev(myFileName, "."))

'strip off extension
myFileName = Left(myFileName, InStrRev(myFileName, ".") - 1)

'Check last x characters for date format
If Right(myFileName, 11) Like "_####-##-##" Then
'strip off previous date
myFileName = Left(myFileName, Len(myFileName) - Len("_####-##-##"))
End If

'add date

myFileName = myFileName & Format(Date, "_yyyy-mm-dd") & myExt

With ThisWorkbook
If myFileName = .Name Then
'same date, just do a save
.Save
MsgBox "Saved using same name"
Else
'don't show overwrite prompt
Application.DisplayAlerts = False
'save as new name
.SaveAs Filename:=.Path & "\" & myFileName, FileFormat:=.FileFormat
Application.DisplayAlerts = True
End If
End With

End Sub

I used a date format of: _yyyy-mm-dd

I find that it's easier for sorting and the underscore makes it easier to read.

 

[Phil Wales]

Thanks Dave - that sounds spot on

This may get you started:

Option Explicit
Sub testme()

Dim myFileName As String
Dim myExt As String

myFileName = ThisWorkbook.Name

'find extension
myExt = Mid(myFileName, InStrRev(myFileName, "."))

'strip off extension
myFileName = Left(myFileName, InStrRev(myFileName, ".") - 1)

'Check last x characters for date format
If Right(myFileName, 11) Like "_####-##-##" Then
'strip off previous date
myFileName = Left(myFileName, Len(myFileName) - Len("_####-##-##"))
End If

'add date

myFileName = myFileName & Format(Date, "_yyyy-mm-dd") & myExt

With ThisWorkbook
If myFileName = .Name Then
'same date, just do a save
.Save
MsgBox "Saved using same name"
Else
'don't show overwrite prompt
Application.DisplayAlerts = False
'save as new name
.SaveAs Filename:=.Path & "\" & myFileName, FileFormat:=.FileFormat
Application.DisplayAlerts = True
End If
End With

End Sub

I used a date format of: _yyyy-mm-dd

I find that it's easier for sorting and the underscore makes it easier to read.

 

[Murph]

Thanks for the code Dave, I have been able to use it in saving a template in
2007.
However it saves the resultant file as .xlsx not macro enabled .xlsm.


Can you show me the adjusted code?

Murph

 

[Dave Peterson]

I don't use xl2007 to help.

But Ron de Bruin can!
http://www.rondebruin.nl/saveas.htm

 

[Murph]

Thanks again Dave.
I couldn't get my head around some of Ron's great code but adapted yours by
storing the macros in Personal macro file and changing your code to Active
rather than This.

Working well so far, fingers crossed!
Murph