PDA

View Full Version : [SOLVED] Error 1004 if we don't want to save



michelle
07-01-2005, 01:08 AM
Hello everybody,

We wrote a vba code and we want to save the excel file, code:


ActiveWorkbook.SaveAs Filename:="D:\weg\Map1.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

If the file exist, a msgbox popsup and ask or we want to overwrite the file.

If we say no an error 1004 occured.

Is there a simple way for get not an error if we choose No? Can we change the the parameters in function "ActiveWorkbook.SaveAs Filename"?

We can use "On Error Resume Next" but we don find it not a correct one

Nice regards,

Michelle. :dunno

Bob Phillips
07-01-2005, 02:10 AM
Hello everybody,

We wrote a vba code and we want to save the excel file, code:

ActiveWorkbook.SaveAs Filename:="D:\weg\Map1.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

If the file exist, a msgbox popsup and ask or we want to overwrite the file.

If we say no an error 1004 occured.

Is there a simple way for get not an error if we choose No? Can we change the the parameters in function "ActiveWorkbook.SaveAs Filename"?

We can use "On Error Resume Next" but we don find it not a correct one


Michelle,

Try


On Error Resume Next
ActiveWorkbook.SaveAs Filename:="D:\weg\Map1.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
On Error GoTo 0

michelle
07-01-2005, 03:27 AM
Thanks xld,

As I said in my question "On Error resume next" is not real proper programming but I am afraid this is the only way.

Thanks,

Michelle. :friends:

Bob Phillips
07-01-2005, 04:02 AM
As I said in my question "On Error resume next" is not real proper programming but I am afraid this is the only way.

What is that supposed to mean?

I know many professional VB and VBA developers, and they use it frequently. The trick is to control it.

Killian
07-01-2005, 05:25 AM
As I said in my question "On Error resume next" is not real proper programmingUnfortunately for VBA programmers, their object model is heavily populated with methods with no return values so determining the success of one isn't as straight-forward as it might be.
For this reason, "On Error" is often the only way to handle a situation and can sometimes be more efficient than the alternatives.
However, I would still treat it's use with caution (particulary in conjuction with GoTo) since it's a sad reality that many people don't realise the pitfalls of it's improper use.

In this example, I think detecting the error in this way is justified although you could switch displayalerts off to force the overwrite. If you need to manage the process in more detail, you could use something like this


Const strFilename As String = "D:\weg\Map1.xls"
Dim strMsgText As String
Dim msgboxresponse As Integer
strMsgText = "The file " & strFilename & " already exists." & "Do you want to replace the existing file?"
msgboxresponse = MsgBox(strMsgText, vbExclamation + vbYesNo, "Microsoft Office Excel")
If msgboxresponse = vbYes Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=strFilename, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
Else
msgboxresponse = MsgBox("File not saved", vbExclamation + vbOKOnly, "Microsoft Office Excel")
End If

Bob Phillips
07-01-2005, 05:44 AM
.script12hide { display: none; }



Unfortunately for VBA programmers, their object model is heavily populated with methods with no return values so determining the success of one isn't as straight-forward as it might be.
For this reason, "On Error" is often the only way to handle a situation and can sometimes be more efficient than the alternatives.
However, I would still treat it's use with caution (particulary in conjuction with GoTo) since it's a sad reality that many people don't realise the pitfalls of it's improper use.

In this example, I think detecting the error in this way is justified although you could switch displayalerts off to force the overwrite. If you need to manage the process in more detail, you could use something like this


Const strFilename As String = "D:\weg\Map1.xls"
Dim strMsgText As String
Dim msgboxresponse As Integer
strMsgText = "The file " & strFilename & " already exists." & "Do you want to replace the existing file?"
msgboxresponse = MsgBox(strMsgText, vbExclamation + vbYesNo, "Microsoft Office Excel")
If msgboxresponse = vbYes Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=strFilename, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
Else
msgboxresponse = MsgBox("File not saved", vbExclamation + vbOKOnly, "Microsoft Office Excel")
End If


Switching displayalerts off in this case is not a good idea, ther can be no cancel then as it takes the default, Yes.

Using it with caution (which means properly) does not make it 'not proper programming'. You have to work with the constraints of any language, and if you know what you are doing, On Error Resume Next is perfectly proper way of programming. My example turned default error handling back on afterwards, so no detritus was left around.

If I needed to know the result I would use error trapping and test the error number



On Error Resume Next
Activeworkbook.SaveAs "C:\myTest\myFile.xls"
If Err.Number Then Msgbox "Error in file save"
On Error Goto 0