file save as

  • Thread starter Thread starter Phil Wales
  • Start date Start date
P

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.
 
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.
 
Thanks Dave - that sounds spot on

Dave Peterson said:
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.
Click to expand...
 
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
 
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
 
Back
Top