Solved: Need to limit choices for the save as name.

[frank_m]

I'm in need of a ThisWorkbook before save event routine, that limits the save as name to only equal the current name, or the current name with the date and time appended to it.

Thanks in advance for your assistance

Edit: Sorry to add complexity to my request. I need to always be able to get back to the original name,
which will always be a single word Name(without a date)

[Ken Puls]

Hi Frank,

What about the directory? Do you always want it saved in the same directory as well, or should the user be able to browse to a new one?

(Forcing a save to the same directory would be a lot easier than giving the user the option to browse too.)

[frank_m]

Hi Ken,

To the same directory will be great. - Sorry I didn't think to mention that.

Thanks for your assistance.

[Ken Puls]

Give this a go:

[VBA]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then
Application.EnableEvents = False
Select Case MsgBox("Would you like to append the date/time to the file name?", vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.SaveAs _
Filename:=ThisWorkbook.Path & _
Application.PathSeparator & _
Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1) & _
Format(Now, " yyyy-mm-dd mm-ss") & _
Right(ThisWorkbook.Name, Len(ThisWorkbook.Name) - InStrRev(ThisWorkbook.Name, ".") + 1)
Case Is = vbNo
ThisWorkbook.Save
Case Is = vbCancel
'Just cancel
End Select
Cancel = True
Application.EnableEvents = True
End If
End Sub[/VBA]

[frank_m]

Hi Ken,

I really appreciate your time working on this. - The code you wrote gets me close.
There are a couple of changes that will make it perfect, if it's not too much trouble.

Currently the workbook name is "Joblog" and when I appended it twice:
the result was "Joblog 2011-04-15 04-45 2011-04-15 04-07"
The time part of it doesn't seem to be a time, as it was about 1:25 am when I ran it. That is fine though, I can fix that myself with a quick google search for date time formats.

The changes I need are that I want the Yes choice to replace the date/time (if any) to make it current - In other words, there should never be more than one date/time in a file name.

If No is selected, I want the date and time stripped off so that it is renamed back to the ordinal name (in this case "Joblog") - The file names are always going to be a single word.

Thank you so much

[Ken Puls]

Sorry, Frank. Posted too late last night!

Try this:
[VBA]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sFileName As String
Dim sFileExt As String
Dim sFileFullName As String

'Set file name here
sFileName = "Joblog"
sFileExt = Right(ThisWorkbook.Name, Len(ThisWorkbook.Name) - InStrRev(ThisWorkbook.Name, ".") + 1)
If SaveAsUI = True Then

'Turn off events to prevent recursive calls
Application.EnableEvents = False

'Ask user what they'd like to do
Select Case MsgBox("Would you like to append the date/time to the file name?", vbYesNoCancel)

Case Is = vbYes
'Append datestampe to file
sFileName = sFileName & Format(Now, " yyyy-mm-dd hh-nn-ss")
sFileFullName = ThisWorkbook.Path & Application.PathSeparator & sFileName & sFileExt
ThisWorkbook.SaveAs sFileFullName

Case Is = vbNo
'Save under original file name
sFileFullName = ThisWorkbook.Path & Application.PathSeparator & sFileName & sFileExt

If ThisWorkbook.FullName = sFileFullName Then
'File already saved with no datestamp. Just needs to be saved
ThisWorkbook.Save
Else
'File needs to be saved under original name
On Error Resume Next
Kill sFileFullName
On Error GoTo 0
ThisWorkbook.SaveAs sFileFullName
End If

Case Is = vbCancel
'User cancelled. Do nothing, as cancel called later
End Select

'Cancel the save as all required saves are complete
Cancel = True
Application.EnableEvents = True
End If
End Sub[/VBA]

[frank_m]

HI Ken,

Thanks a bunch for that. -- It's working great.

Can't thank you enough

And I really appreciate you taking the extra time in adding the well written code comments.

Hope you have a great day.