Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained WorkbookBeforeSave SaveAs Error Causes Excel to Crash
I have created a fairly extensive (for me anyway) code set that will
ultimately become an Excel 2003 addin for my team. We have recently begun an effort to implement strict ISO requirements for document version control; I'm trying to automate this process with this future addin. So, what I've done is created a series of custom fields to contain the required components of my filenames and am using the code below to automatically update the version & subsequently save as a new file with the version numbers in the file name. The only problem is that something about the ActiveWorkbook.SaveAs makes Excel crash every time it SavesAs a new (because of the changed ver. numbers appended to the filename) file. Once I restart Excel, everything is fine and even the new file works just fine. Strangely enough though, if I manually manipulate the version fields so that when I save it, it would create a filename of a file that already exists, it asks me if I wanted to replace, and then everything works fine. Obviously, I want to create a new file each and every save, but I can't seem to figure out what's making it crash. The same code also works in 2007 on my PC at home. Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Determine Dim UpdAnswer, Ext As Variant UpdAnswer = MsgBox("Is this revision a major update?", vbQuestion + vbYesNoCancel, _ "Version Verification") Ext = Right(ActiveWorkbook.Name, 4) If UpdAnswer = vbCancel Then Exit Sub ElseIf UpdAnswer = vbYes Then ActiveWorkbook.CustomDocumentProperties("MajNo") = _ ActiveWorkbook.CustomDocumentProperties("MajNo") + 1 ActiveWorkbook.CustomDocumentProperties("MinNo") = 0 ElseIf UpdAnswer = vbNo Then ActiveWorkbook.CustomDocumentProperties("MinNo") = _ ActiveWorkbook.CustomDocumentProperties("MinNo") + 1 End If Application.EnableEvents = False ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & ActiveWorkbook.CustomDocumentProperties("DocName") & _ "_v" & ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _ ActiveWorkbook.CustomDocumentProperties("MinNo") & Ext) Application.EnableEvents = True Cancel = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained WorkbookBeforeSave SaveAs Error Causes Excel to Crash
First, I wouldn't rely on the workbook you're saving being the activeworkbook.
Since you're tying into that App_WorkbookBeforeSave and passing the workbook (via the wb parm), I'd use: wb.custom... instead of ActiveWorkbook.Custom... (for all those activeworkbook references) If you want to stop the "are you sure you want to overwrite" prompts: application.displayalerts = false 'added Application.EnableEvents = False wb.SaveAs wb.Path & "\" & wb.CustomDocumentProperties("DocName") & _ "_v" & wb.CustomDocumentProperties("MajNo") & "." & _ wb.CustomDocumentProperties("MinNo") & Ext Application.EnableEvents = True application.displayalerts = true 'added (No need for parentheses around the filename in the .SaveAs line, either.) But I don't think will stop excel from crashing. I don't have a guess for that. JT Klipfer wrote: I have created a fairly extensive (for me anyway) code set that will ultimately become an Excel 2003 addin for my team. We have recently begun an effort to implement strict ISO requirements for document version control; I'm trying to automate this process with this future addin. So, what I've done is created a series of custom fields to contain the required components of my filenames and am using the code below to automatically update the version & subsequently save as a new file with the version numbers in the file name. The only problem is that something about the ActiveWorkbook.SaveAs makes Excel crash every time it SavesAs a new (because of the changed ver. numbers appended to the filename) file. Once I restart Excel, everything is fine and even the new file works just fine. Strangely enough though, if I manually manipulate the version fields so that when I save it, it would create a filename of a file that already exists, it asks me if I wanted to replace, and then everything works fine. Obviously, I want to create a new file each and every save, but I can't seem to figure out what's making it crash. The same code also works in 2007 on my PC at home. Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Determine Dim UpdAnswer, Ext As Variant UpdAnswer = MsgBox("Is this revision a major update?", vbQuestion + vbYesNoCancel, _ "Version Verification") Ext = Right(ActiveWorkbook.Name, 4) If UpdAnswer = vbCancel Then Exit Sub ElseIf UpdAnswer = vbYes Then ActiveWorkbook.CustomDocumentProperties("MajNo") = _ ActiveWorkbook.CustomDocumentProperties("MajNo") + 1 ActiveWorkbook.CustomDocumentProperties("MinNo") = 0 ElseIf UpdAnswer = vbNo Then ActiveWorkbook.CustomDocumentProperties("MinNo") = _ ActiveWorkbook.CustomDocumentProperties("MinNo") + 1 End If Application.EnableEvents = False ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & ActiveWorkbook.CustomDocumentProperties("DocName") & _ "_v" & ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _ ActiveWorkbook.CustomDocumentProperties("MinNo") & Ext) Application.EnableEvents = True Cancel = True End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained WorkbookBeforeSave SaveAs Error Causes Excel to Cr
Thanks Dave,
Noted on the wb usage; actually, my requirements grew into a little more of a robust functionality that I solved by implementing a custom user form. When executing through that means, I don't seem to have any problems. Thanks again for your help!--JT "Dave Peterson" wrote: First, I wouldn't rely on the workbook you're saving being the activeworkbook. Since you're tying into that App_WorkbookBeforeSave and passing the workbook (via the wb parm), I'd use: wb.custom... instead of ActiveWorkbook.Custom... (for all those activeworkbook references) If you want to stop the "are you sure you want to overwrite" prompts: application.displayalerts = false 'added Application.EnableEvents = False wb.SaveAs wb.Path & "\" & wb.CustomDocumentProperties("DocName") & _ "_v" & wb.CustomDocumentProperties("MajNo") & "." & _ wb.CustomDocumentProperties("MinNo") & Ext Application.EnableEvents = True application.displayalerts = true 'added (No need for parentheses around the filename in the .SaveAs line, either.) But I don't think will stop excel from crashing. I don't have a guess for that. JT Klipfer wrote: I have created a fairly extensive (for me anyway) code set that will ultimately become an Excel 2003 addin for my team. We have recently begun an effort to implement strict ISO requirements for document version control; I'm trying to automate this process with this future addin. So, what I've done is created a series of custom fields to contain the required components of my filenames and am using the code below to automatically update the version & subsequently save as a new file with the version numbers in the file name. The only problem is that something about the ActiveWorkbook.SaveAs makes Excel crash every time it SavesAs a new (because of the changed ver. numbers appended to the filename) file. Once I restart Excel, everything is fine and even the new file works just fine. Strangely enough though, if I manually manipulate the version fields so that when I save it, it would create a filename of a file that already exists, it asks me if I wanted to replace, and then everything works fine. Obviously, I want to create a new file each and every save, but I can't seem to figure out what's making it crash. The same code also works in 2007 on my PC at home. Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Determine Dim UpdAnswer, Ext As Variant UpdAnswer = MsgBox("Is this revision a major update?", vbQuestion + vbYesNoCancel, _ "Version Verification") Ext = Right(ActiveWorkbook.Name, 4) If UpdAnswer = vbCancel Then Exit Sub ElseIf UpdAnswer = vbYes Then ActiveWorkbook.CustomDocumentProperties("MajNo") = _ ActiveWorkbook.CustomDocumentProperties("MajNo") + 1 ActiveWorkbook.CustomDocumentProperties("MinNo") = 0 ElseIf UpdAnswer = vbNo Then ActiveWorkbook.CustomDocumentProperties("MinNo") = _ ActiveWorkbook.CustomDocumentProperties("MinNo") + 1 End If Application.EnableEvents = False ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & ActiveWorkbook.CustomDocumentProperties("DocName") & _ "_v" & ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _ ActiveWorkbook.CustomDocumentProperties("MinNo") & Ext) Application.EnableEvents = True Cancel = True End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexplained WorkbookBeforeSave SaveAs Error Causes Excel to Cr
I think that's a better approach, too.
I think making a dedicated macro/userform to do something like this makes a lot more sense. JT Klipfer wrote: Thanks Dave, Noted on the wb usage; actually, my requirements grew into a little more of a robust functionality that I solved by implementing a custom user form. When executing through that means, I don't seem to have any problems. Thanks again for your help!--JT "Dave Peterson" wrote: First, I wouldn't rely on the workbook you're saving being the activeworkbook. Since you're tying into that App_WorkbookBeforeSave and passing the workbook (via the wb parm), I'd use: wb.custom... instead of ActiveWorkbook.Custom... (for all those activeworkbook references) If you want to stop the "are you sure you want to overwrite" prompts: application.displayalerts = false 'added Application.EnableEvents = False wb.SaveAs wb.Path & "\" & wb.CustomDocumentProperties("DocName") & _ "_v" & wb.CustomDocumentProperties("MajNo") & "." & _ wb.CustomDocumentProperties("MinNo") & Ext Application.EnableEvents = True application.displayalerts = true 'added (No need for parentheses around the filename in the .SaveAs line, either.) But I don't think will stop excel from crashing. I don't have a guess for that. JT Klipfer wrote: I have created a fairly extensive (for me anyway) code set that will ultimately become an Excel 2003 addin for my team. We have recently begun an effort to implement strict ISO requirements for document version control; I'm trying to automate this process with this future addin. So, what I've done is created a series of custom fields to contain the required components of my filenames and am using the code below to automatically update the version & subsequently save as a new file with the version numbers in the file name. The only problem is that something about the ActiveWorkbook.SaveAs makes Excel crash every time it SavesAs a new (because of the changed ver. numbers appended to the filename) file. Once I restart Excel, everything is fine and even the new file works just fine. Strangely enough though, if I manually manipulate the version fields so that when I save it, it would create a filename of a file that already exists, it asks me if I wanted to replace, and then everything works fine. Obviously, I want to create a new file each and every save, but I can't seem to figure out what's making it crash. The same code also works in 2007 on my PC at home. Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Determine Dim UpdAnswer, Ext As Variant UpdAnswer = MsgBox("Is this revision a major update?", vbQuestion + vbYesNoCancel, _ "Version Verification") Ext = Right(ActiveWorkbook.Name, 4) If UpdAnswer = vbCancel Then Exit Sub ElseIf UpdAnswer = vbYes Then ActiveWorkbook.CustomDocumentProperties("MajNo") = _ ActiveWorkbook.CustomDocumentProperties("MajNo") + 1 ActiveWorkbook.CustomDocumentProperties("MinNo") = 0 ElseIf UpdAnswer = vbNo Then ActiveWorkbook.CustomDocumentProperties("MinNo") = _ ActiveWorkbook.CustomDocumentProperties("MinNo") + 1 End If Application.EnableEvents = False ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & ActiveWorkbook.CustomDocumentProperties("DocName") & _ "_v" & ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _ ActiveWorkbook.CustomDocumentProperties("MinNo") & Ext) Application.EnableEvents = True Cancel = True End Sub -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#N/A Error unexplained | Excel Worksheet Functions | |||
Unexplained "Bad Record Length" error / bug? | Excel Programming | |||
Excel XP and WorkbookBeforeSave | Excel Programming | |||
Excel crash error code | Excel Programming | |||
VBA Compiler Error Cause Excel to Crash | Excel Programming |