Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeSave() 1004 Error
All,
I know that this has been asked a number of times in other posts, but I have yet to put it together in the context that I need. I am using Excel97 on XP. I would like to do a SaveAs any time the user attempts any sort of Save. My plan was to use the Workbook_BeforeSave event to prompt the user to enter another filename each time they try to save and process accordingly. My code is listed below. Any help would be appreciated. TIA, Joel Brewster Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) On Error GoTo ErrHandler Call SaveAsDBF Cancel = True ErrHandler: If Err.Number < 0 Then Cancel = True End If End Sub Public Sub SaveAsDBF() Dim strFileName As String On Error GoTo ErrHandler strFileName = Application.GetSaveAsFilename("*.dbf", "dBase Files (*.dbf),*.dbf", 7, "Save DBF") If UCase(strFileName) < "FALSE" Then Application.EnableEvents = False Application.DisplayAlerts = False ActiveWorkbook.SaveAs FileName:=strFileName, FileFormat:=xlDBF4 Application.DisplayAlerts = True Application.EnableEvents = True End If ErrHandler: If Err.Number < 0 Then MsgBox "Error in SaveAsDBF: " & Err.Number & " - " & Err.Description Err.Clear Application.DisplayAlerts = True Application.EnableEvents = True End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeSave() 1004 Error
This doesn't prompt for a new filename, but gets the user to browse for a
filename (which they can then input). What doesn't happen that you want? Did you put the code in the Thisworkbook code module. -- HTH RP (remove nothere from the email address if mailing direct) wrote in message ups.com... All, I know that this has been asked a number of times in other posts, but I have yet to put it together in the context that I need. I am using Excel97 on XP. I would like to do a SaveAs any time the user attempts any sort of Save. My plan was to use the Workbook_BeforeSave event to prompt the user to enter another filename each time they try to save and process accordingly. My code is listed below. Any help would be appreciated. TIA, Joel Brewster Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) On Error GoTo ErrHandler Call SaveAsDBF Cancel = True ErrHandler: If Err.Number < 0 Then Cancel = True End If End Sub Public Sub SaveAsDBF() Dim strFileName As String On Error GoTo ErrHandler strFileName = Application.GetSaveAsFilename("*.dbf", "dBase Files (*.dbf),*.dbf", 7, "Save DBF") If UCase(strFileName) < "FALSE" Then Application.EnableEvents = False Application.DisplayAlerts = False ActiveWorkbook.SaveAs FileName:=strFileName, FileFormat:=xlDBF4 Application.DisplayAlerts = True Application.EnableEvents = True End If ErrHandler: If Err.Number < 0 Then MsgBox "Error in SaveAsDBF: " & Err.Number & " - " & Err.Description Err.Clear Application.DisplayAlerts = True Application.EnableEvents = True End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeSave() 1004 Error
Thanks for your response Bob. The code is located in the ThisWorkbook
module in Sub Workbook_BeforeSave and does prompt for the new filename and does not prompt for an overwrite. However, whenever I compile it and run without debugging an error is raised indicating a 1004 - Method SaveAs of object '_Workbook' failed. Everything that I've read indicates that the above code should work but it does not for me. Any ideas would be appreciated. Thanks, Joel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeSave() 1004 Error
I forgot to mention that my workbook is password protected. I read
somewhere that the protection feature might cause strange behavior so I disable it and everything works well. It would be nice if I could protect the code from modification though if anyone has ideas. TIA Joel |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeSave() 1004 Error
Could you not turn protection off in the code, do your stuff, then
re-protect? -- HTH RP (remove nothere from the email address if mailing direct) wrote in message ups.com... I forgot to mention that my workbook is password protected. I read somewhere that the protection feature might cause strange behavior so I disable it and everything works well. It would be nice if I could protect the code from modification though if anyone has ideas. TIA Joel |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeSave() 1004 Error
Goof idea but I already tried that and it still received the error.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
Runtime error '1004' General ODBC error | New Users to Excel | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
naming tab macro error runtime error 1004 | Excel Programming | |||
Run time error 1004 General ODCB Error | Excel Programming |