GetSaveAs problem when filename already exists

Topic

New Reply

The following code works great to prompt the user for a saveas file name except when the filename already exists and the user answers “no” to the question “A file named ‘C:Fieldtools1235.xls’ already exists in this location. Do you want to replace it? Yes, No, Cancel”

All works well if we answer yes or Cancel, but if I answer “No” I get the the following debug message:
Run time error 1004, Method of “SaveAs” of object ‘_Workbook failed.

The debug brings me to this line in the above code:
ActiveWorkbook.SaveAs Filename:=DataFileName

How do I get the “No” click of the button to return “false” as the filename. (Or remove the No as an option?)

Thanks for any help you may have!

DataFileName = Application.GetSaveAsFilename(TractNo & “.xls”, “Excel Files (*.xls),.xls,All Files, *.*”)
If DataFileName “False” Then ‘if user didn’t press cancel
ActiveWorkbook.SaveAs Filename:=DataFileName
End If

Reply | Quote

Replies

You could use the Dir function to determine if the file exists before doing the SaveAs and then display your own MsgBox to find out what the user wants to do.

Reply | Quote

Hi Annie,

This is a shot in the dark, but you might try removing the quotation marks around the “False” in your statement.

I believe it should read like this:

If DataFileName False Then

HTH,
Mike

Reply | Quote

I just looked at your message again when I was a little more wide awake, and I noticed several things. First, Michael is correct, you do need to remove the quotes from around “Fales” in your If statement.

When I reread your post, I realized that the message that you are refering to is generated from the SaveAs method, not from GetSaveAsFilename. Therefore, clicking on the No button will never set Filename to False since that is set from GetSaveAsFilename. Clicking on the No button causes the error in SaveAs before control is returned to your code to check anything. There are a number of possible ways around this.

1- If you always want to replace the duplicate file without displaying the error message to the user and giving him a chance to click on the No button, then you can use the following code:

    DataFileName = Application.GetSaveAsFilename(TractNo & ".xls", _
      "Excel Files (*.xls),.xls,All Files, *.*")
    If DataFileName  False Then 'if user didn't press cancel
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:=DataFileName
        Application.DisplayAlerts = True
    End If 

2- If you never want the user to be able to replace an existing file, then you can do something like this:

Dim StrWk As String
    Do
        DataFileName = Application.GetSaveAsFilename(TractNo & ".xls", _
          "Excel Files (*.xls),.xls,All Files, *.*")
        If DataFileName = False Exit Do
        strWk = Dir(DataFileName, vbNormal)
        If strWk  "" Then
            MsgBox "Duplicate file name not allowed."
        End If
    Loop While strWk  ""
    If DataFileName  False Then 'if user didn't press cancel
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:=DataFileName
        Application.DisplayAlerts = True
    End If 

3- If you want to give the user the option of replacing an existing file, then you could do something like this:

Dim StrWk As String
Dim iReturn as Integer
    Do
        DataFileName = Application.GetSaveAsFilename(TractNo & ".xls", _
          "Excel Files (*.xls),.xls,All Files, *.*")
        If DataFileName = False Exit Do
        strWk = Dir(DataFileName, vbNormal)
        If strWk  "" Then
            iReturn = MsgBox "Duplicate file, do you want to replace?", vbQuestion + vbYesNo
            If iReturn = vbYes Exit DO
        End If
    Loop While strWk  ""
    If DataFileName  False Then 'if user didn't press cancel
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:=DataFileName
        Application.DisplayAlerts = True
    End If 

4- You can just ignore the error:

    DataFileName = Application.GetSaveAsFilename(TractNo & ".xls", _
      "Excel Files (*.xls),.xls,All Files, *.*")
    If DataFileName  False Then 'if user didn't press cancel
        On Error Resume Next
        ActiveWorkbook.SaveAs Filename:=DataFileName
        If Err.Number  0 Then
            If Err.Number = 1004 Then
                ' Do whatever you want for the No button error.
            Else
                ' Do whatever you want for other errors.
            End If
        End If
    End If 

None of the above code was tested.

Reply | Quote

Legare… thank you very much for your response. Taking the quotes away from the word “False” in this situation causes an error.

I will try the various suggestions you listed and will post the solution that works! Thank you (as always) for your kind assistance.

Reply | Quote