Error 1004 if we don't want to save

[michelle]

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.

[Bob Phillips]

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]

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.

[Bob Phillips]

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]

As I said in my question "On Error resume next" is not real proper programming

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

[Bob Phillips]

.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