-
Override the built in SAVE
Wondering if someone may know what is not quite right with the code....
Basicall, if someone hit the SAVE button, I want to override the built in SAVE function with my own, so it gives a versioning of filenames. If I run it as is (traced it and all lines get executed), I don't get my file, nothing, very strange. However, if I attach the code to a button, I get exactly the outcome that I anticipated.
[vba]
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
SaveWithVersioning
End Sub
sub SaveWithVersioning
Dim sFileName As String
Dim sCurrentName As String
sCurrentName = Worksheets("SIMOPS").Cells(2, "I")
sFileName = CleanFileName(sCurrentName)
sFileName = ThisWorkbook.Path & "\" & sFileName & "-" & Format(Now, "mmm-dd-yyyy") & ".xls"
If DiskFileExists(sFileName) Then
ThisWorkbook.Save
Else
ThisWorkbook.SaveAs sFileName
End If
end sub
[/vba]
Last edited by JKwan; 03-28-2011 at 01:09 PM.
-
If a macro errors with Application.EnableEvents=False, run the Application.EnableEvents=True in the Immediate window when testing.
Since I don't have the functions that you posted, I tweaked it a bit.
[VBA]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
SaveWithVersioning
End Sub
Private Sub SaveWithVersioning()
Dim sFileName As String
Dim sCurrentName As String
Application.EnableEvents = False
sCurrentName = Worksheets("SIMOPS").Cells(2, "I").Value2
'sFileName = CleanFileName(sCurrentName)
sFileName = sCurrentName
sFileName = ThisWorkbook.Path & "\" & sFileName & "-" & Format(Now, "mmm-dd-yyyy") & ".xlsm"
If Dir(sFileName) <> "" Then
ThisWorkbook.Save
Else
ThisWorkbook.SaveAs sFileName, xlOpenXMLWorkbookMacroEnabled
End If
Application.EnableEvents = True
End Sub[/VBA]
-
Thank you Kenneth for responding, I had more or less like what you posted (by turning on/off the Application Events), it did not work. It is strange!
-
The important difference is
Cancel = True
to abort the Workbook Save
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Sorry, forgot to mention that, I did have Cancel = True in the BeforeSave module. What gets me is that, if I click the SAVE button, I see all the execution and it hit the "SAVE AS" line, it just don't save. If I put the code into a button, works like a charm!
-
It works for me. When the file has not been saved, it will save to your root folder of C:. This is because ThisWorkbook.Path has no path. You might want to use check for that and use curdir or prompt the user for a path.
-
Thanks again for it may be in root of C, however, no go, maybe because I don't have access to root of C. I don't have admin access on my PC :-(.
Another point, I am using Excel 2003, looks like you are using 2007 or 2010, maybe this is another reason? I will continue to hack at it, if not, I will do it my original way, force the user to click my SAVE button, thanks again (hoping others have more ideas for me to try)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules