Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Tullahoma, Tennessee, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    GetSaveAs problem when filename already exists

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetSaveAs problem when filename already exists

    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.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetSaveAs problem when filename already exists

    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

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetSaveAs problem when filename already exists

    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:

    <pre> 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
    </pre>


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

    <pre>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
    </pre>


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

    <pre>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
    </pre>


    4- You can just ignore the error:

    <pre> 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
    </pre>


    None of the above code was tested.
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Jan 2001
    Location
    Tullahoma, Tennessee, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetSaveAs problem when filename already exists

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •