Save files as date & time by default! This may be simple and I'm trying to figure out myself too..

[pedie]

When i save the WORKBOOK's name by default it save as BOOK1, but I want it to be today's (=now) date and time instead on Book1...Is that possible...

I check the options the can't seems to locate it


Please help thanks!
Ped

 

[pedie]

Sorry VoG, I was cross posting ....

When i run the code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not SaveAsUI Then
    Cancel = True
    Application.EnableEvents = False
    ActiveWorkbook.SaveAs Filename[B][COLOR=red]:[/COLOR][/B]=Format(Now, "dd-mm-yyyy hh-mm-ss")
    Application.EnableEvents = True
End If
End Sub

it is showing Runtime error '1004' Vb project and XLM sheets cannot be saved in macro free workbook, but it is my default settigng to save any workbook in "Mecro enabled"....

I want no new file to be created when i click on save and close. I want this to happen only when i save as new file...is that possible???

Thanks again VoG!

 

[VoG]

That is strange because for me it saved as an xlsm file. Anyway, that was slapdash coding .

Try this - it will save as the current date and time only when you Save As.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
    Cancel = True
    Application.EnableEvents = False
    ActiveWorkbook.SaveAs Filename:=Format(Now, "dd-mm-yyyy hh-mmss") & ".xlsm"
    Application.EnableEvents = True
End If
End Sub

 

[pedie]

Now when i open a new workbook and then close it ask
'Do you want to save?'
I click on Yes
And it saved as Book1 not time and date and no error message...

 

[VoG]

I am baffled

This works but it prompts twice despite several ways of trying to stop that from happening.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ActiveWorkbook.Saved Then
    Cancel = True
    Exit Sub
End If
If SaveAsUI Or InStr(Me.Name, ".") = 0 Then
    Cancel = True
    Application.EnableEvents = False
    ActiveWorkbook.SaveAs Filename:=Format(Now, "dd-mm-yyyy hh-mm-ss") & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    Application.EnableEvents = True
    ActiveWorkbook.Saved = True
End If
End Sub

 

[pedie]

Vog! I understand u're baffled...

If SaveAsUI Or InStr(Me.Name, ".") = 0 Then

this code is not giving any error mesage...
When I save as it is prompting 2wice which is okay but when click yes..it

saved the file name as Book1

 

[VoG]

I created a new workbook, entered some data then clicked the X, clicked Yes to save the file then Cancel for the second prompt. It saved the file as the date & time. I then used Save As and again it saved as date and time with no prompts.

 

[pedie]

Pls gimmi a moment..VoG...lemmi try again..! thanks

 

[pedie]

Vog, Great....!

Thank you so much again

I'm sure i got you tight up just for this thread...! THanks for helping and making me understand how things worked....


Good Day!!!

 

[VoG]

You are welcome

I just cannot understand why it prompts twice. But it is good practice to save before closing - it is too easy to click the wrong button and lose a lot of work.

 

[pedie]

I am not going to use this untill I become very familiar with how it works...prompting is fine with me...that is okay..

You're great....!

Thanks again!