+ Reply to Thread
Results 1 to 8 of 8

SaveAs Problem

  1. #1
    Don Lloyd
    Guest

    SaveAs Problem

    Hi All,

    Happy New Year - belated but well meant

    I'm programmatically using the SaveAs Dialog Box to allow the user to decide
    where to save a file.

    If the chosen path already exists, SaveAs points this out.
    Fine, except that, if the option to not overwrite the file is chosen, an
    error 1004 message appears. Clicking Help helpfully displays a blank Help
    screen.

    In short, how do I handle this error. Code example as follows.

    Sub UserFileSave()
    Dim Fname, Suggestion, Hdr
    Suggestion = "MyFile " & Format(Date, "dd mmm yy")
    Hdr = "Please choose a Location and Name then click Save."
    Fname = Application.GetSaveAsFilename(Suggestion, fileFilter:="Excel File
    (*.xls), *.xls)", Title:=Hdr)
    If Fname = False Then
    'Handle Cancel
    UserCancel
    Else
    ThisWorkbook.SaveAs FileName:=Fname
    End If
    End Sub

    Regards,
    Don
    ( XL97 Win98 / XL2003 WinXp )



  2. #2
    Norman Jones
    Guest

    Re: SaveAs Problem

    Hi Don,

    Try:

    If Fname <> False Then
    ThisWorkbook.SaveAs Filename:=Fname
    Else
    ' User Cancelled - don't save
    End If

    ---
    Regards,
    Norman



    "Don Lloyd" <[email protected]> wrote in message
    news:[email protected]...
    > Hi All,
    >
    > Happy New Year - belated but well meant
    >
    > I'm programmatically using the SaveAs Dialog Box to allow the user to
    > decide where to save a file.
    >
    > If the chosen path already exists, SaveAs points this out.
    > Fine, except that, if the option to not overwrite the file is chosen, an
    > error 1004 message appears. Clicking Help helpfully displays a blank Help
    > screen.
    >
    > In short, how do I handle this error. Code example as follows.
    >
    > Sub UserFileSave()
    > Dim Fname, Suggestion, Hdr
    > Suggestion = "MyFile " & Format(Date, "dd mmm yy")
    > Hdr = "Please choose a Location and Name then click Save."
    > Fname = Application.GetSaveAsFilename(Suggestion, fileFilter:="Excel File
    > (*.xls), *.xls)", Title:=Hdr)
    > If Fname = False Then
    > 'Handle Cancel
    > UserCancel
    > Else
    > ThisWorkbook.SaveAs FileName:=Fname
    > End If
    > End Sub
    >
    > Regards,
    > Don
    > ( XL97 Win98 / XL2003 WinXp )
    >




  3. #3
    Claud Balls
    Guest

    Re: SaveAs Problem

    This is kind of hokey, and a user could still induce the error, but it
    gives you a little buffer.

    Sub UserFileSave()
    Dim Fname, Suggestion, Hdr
    Suggestion = "MyFile " & Format(Date, "dd mmm yy")
    Hdr = "Please choose a Location and Name then click Save."
    Fname = Application.GetSaveAsFilename(Suggestion, fileFilter:="Excel
    File(*.xls), *.xls)", Title:=Hdr)
    If Fname = False Then
    'Handle Cancel
    'UserCancel
    ElseIf Dir(Fname) = "" Then
    ThisWorkbook.saveas Filename:=Fname
    Else
    response = MsgBox("File Exists, Save anyway?", vbYesNo)
    If response = vbYes Then
    ThisWorkbook.saveas Filename:=Fname
    Else
    Exit Sub
    End If
    End If
    End Sub



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

  4. #4
    Don Lloyd
    Guest

    Re: SaveAs Problem

    Thank you Norman and Claud

    Unfortunately neither method works properly as the error keeps coming up
    when the offer to replace the file is declined.

    For the time being I've managed to overcome it with "unqualified" error
    trapping and then using recursion.

    Thanks for your time and effort.

    regards,
    Don

    "Claud Balls" <[email protected]> wrote in message
    news:[email protected]...
    > This is kind of hokey, and a user could still induce the error, but it
    > gives you a little buffer.
    >
    > Sub UserFileSave()
    > Dim Fname, Suggestion, Hdr
    > Suggestion = "MyFile " & Format(Date, "dd mmm yy")
    > Hdr = "Please choose a Location and Name then click Save."
    > Fname = Application.GetSaveAsFilename(Suggestion, fileFilter:="Excel
    > File(*.xls), *.xls)", Title:=Hdr)
    > If Fname = False Then
    > 'Handle Cancel
    > 'UserCancel
    > ElseIf Dir(Fname) = "" Then
    > ThisWorkbook.saveas Filename:=Fname
    > Else
    > response = MsgBox("File Exists, Save anyway?", vbYesNo)
    > If response = vbYes Then
    > ThisWorkbook.saveas Filename:=Fname
    > Else
    > Exit Sub
    > End If
    > End If
    > End Sub
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!




  5. #5
    Norman Jones
    Guest

    Re: SaveAs Problem

    Hi Don,

    Revising your posted nacro to reflect my suggestion, gives:

    Sub UserFileSave()
    Dim Fname, Suggestion, Hdr
    Suggestion = "MyFile " & Format(Date, "dd mmm yy")
    Hdr = "Please choose a Location and Name then click Save."
    Fname = Application.GetSaveAsFilename(Suggestion, _
    fileFilter:="Excel File(*.xls), *.xls)", Title:=Hdr)
    If Fname <> False Then
    ThisWorkbook.SaveAs Filename:=Fname
    Else
    ' User Cancelled - dont save
    End If
    End Sub

    This did not error for me.

    ---
    Regards,
    Norman



    "Don Lloyd" <[email protected]> wrote in message
    news:%[email protected]...
    > Thank you Norman and Claud
    >
    > Unfortunately neither method works properly as the error keeps coming up
    > when the offer to replace the file is declined.
    >
    > For the time being I've managed to overcome it with "unqualified" error
    > trapping and then using recursion.
    >
    > Thanks for your time and effort.
    >
    > regards,
    > Don
    >




  6. #6
    Don Lloyd
    Guest

    Re: SaveAs Problem

    Hi Norman,

    I've tried your code as is.
    When the message saying that the file already exists shows and I choose not
    to replace it, the error 1004 appears, which is fatal if not handled.

    regards,
    Don

    "Norman Jones" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi Don,
    >
    > Revising your posted nacro to reflect my suggestion, gives:
    >
    > Sub UserFileSave()
    > Dim Fname, Suggestion, Hdr
    > Suggestion = "MyFile " & Format(Date, "dd mmm yy")
    > Hdr = "Please choose a Location and Name then click Save."
    > Fname = Application.GetSaveAsFilename(Suggestion, _
    > fileFilter:="Excel File(*.xls), *.xls)", Title:=Hdr)
    > If Fname <> False Then
    > ThisWorkbook.SaveAs Filename:=Fname
    > Else
    > ' User Cancelled - dont save
    > End If
    > End Sub
    >
    > This did not error for me.
    >
    > ---
    > Regards,
    > Norman
    >
    >
    >
    > "Don Lloyd" <[email protected]> wrote in message
    > news:%[email protected]...
    >> Thank you Norman and Claud
    >>
    >> Unfortunately neither method works properly as the error keeps coming up
    >> when the offer to replace the file is declined.
    >>
    >> For the time being I've managed to overcome it with "unqualified" error
    >> trapping and then using recursion.
    >>
    >> Thanks for your time and effort.
    >>
    >> regards,
    >> Don
    >>

    >
    >




  7. #7
    Sharad Naik
    Guest

    Re: SaveAs Problem

    Try below code. Changes marked with '<-----

    Sub UserFileSave()
    Dim Fname, Suggestion, Hdr
    Dim fs '<-------

    Suggestion = "MyFile " & Format(Date, "dd mmm yy")
    Hdr = "Please choose a Location and Name then click Save."

    getFname: '<----- added label

    Fname = Application.GetSaveAsFilename(Suggestion, fileFilter:="Excel File
    (*.xls), *.xls)", Title:=Hdr)
    If Fname = False Then
    'Handle Cancel
    UserCancel
    Else
    '<--------- new lines below
    Set fs = CreateObject("Scripting.FileSystemObject")
    If fs.FileExists(Fname) Or fs.FlieExists(Fname & ".xls") Then
    Select Case MsgBox ("File " & Fname & " already exists. " _
    & "Do you want to replace it?" , vbYesNoCancel)
    Case vbYes
    On Error Resume Next
    Kill Fname
    Kill Fname & ".xls"
    On Error GoTo 0
    Case vbNo
    GoTo getFname: 'get a new name then
    Case vbCancel
    Exit Sub 'used canceled
    End Select
    End If

    ThisWorkbook.SaveAs FileName:=Fname
    End If
    End Sub

    "Don Lloyd" <[email protected]> wrote in message
    news:[email protected]...
    > Hi Norman,
    >
    > I've tried your code as is.
    > When the message saying that the file already exists shows and I choose
    > not to replace it, the error 1004 appears, which is fatal if not handled.
    >
    > regards,
    > Don
    >
    > "Norman Jones" <[email protected]> wrote in message
    > news:%[email protected]...
    >> Hi Don,
    >>
    >> Revising your posted nacro to reflect my suggestion, gives:
    >>
    >> Sub UserFileSave()
    >> Dim Fname, Suggestion, Hdr
    >> Suggestion = "MyFile " & Format(Date, "dd mmm yy")
    >> Hdr = "Please choose a Location and Name then click Save."
    >> Fname = Application.GetSaveAsFilename(Suggestion, _
    >> fileFilter:="Excel File(*.xls), *.xls)", Title:=Hdr)
    >> If Fname <> False Then
    >> ThisWorkbook.SaveAs Filename:=Fname
    >> Else
    >> ' User Cancelled - dont save
    >> End If
    >> End Sub
    >>
    >> This did not error for me.
    >>
    >> ---
    >> Regards,
    >> Norman
    >>
    >>
    >>
    >> "Don Lloyd" <[email protected]> wrote in message
    >> news:%[email protected]...
    >>> Thank you Norman and Claud
    >>>
    >>> Unfortunately neither method works properly as the error keeps coming up
    >>> when the offer to replace the file is declined.
    >>>
    >>> For the time being I've managed to overcome it with "unqualified" error
    >>> trapping and then using recursion.
    >>>
    >>> Thanks for your time and effort.
    >>>
    >>> regards,
    >>> Don
    >>>

    >>
    >>

    >
    >




  8. #8
    Don Lloyd
    Guest

    Re: SaveAs Problem

    Thank you Sharad, it works ! There is no end to man's ingenuity !
    I had managed a workaround - shown below as a matter of interest.

    Sub UserFileSave()
    Dim Fname, Suggestion, Hdr
    Suggestion = "MyFile " & Format(Date, "dd mmm yy")
    Hdr = "Please choose a Location and Name then click Save."
    On Error GoTo ERRH
    Fname = Application.GetSaveAsFilename(Suggestion, fileFilter:="Excel
    File(*.xls), *.xls)", Title:=Hdr)
    If Fname = False Then
    'Handle Cancel
    'UserCancel
    Else
    ThisWorkbook.SaveAs Filename:=Fname
    End If
    Exit Sub
    ERRH:
    On Error Goto 0
    MsgBox "Try another Name"
    UserFileSave
    End Sub

    Thanks again and regards,
    Don

    "Sharad Naik" <[email protected]> wrote in message
    news:[email protected]...
    > Try below code. Changes marked with '<-----
    >
    > Sub UserFileSave()
    > Dim Fname, Suggestion, Hdr
    > Dim fs '<-------
    >
    > Suggestion = "MyFile " & Format(Date, "dd mmm yy")
    > Hdr = "Please choose a Location and Name then click Save."
    >
    > getFname: '<----- added label
    >
    > Fname = Application.GetSaveAsFilename(Suggestion, fileFilter:="Excel File
    > (*.xls), *.xls)", Title:=Hdr)
    > If Fname = False Then
    > 'Handle Cancel
    > UserCancel
    > Else
    > '<--------- new lines below
    > Set fs = CreateObject("Scripting.FileSystemObject")
    > If fs.FileExists(Fname) Or fs.FlieExists(Fname & ".xls") Then
    > Select Case MsgBox ("File " & Fname & " already exists. " _
    > & "Do you want to replace it?" , vbYesNoCancel)
    > Case vbYes
    > On Error Resume Next
    > Kill Fname
    > Kill Fname & ".xls"
    > On Error GoTo 0
    > Case vbNo
    > GoTo getFname: 'get a new name then
    > Case vbCancel
    > Exit Sub 'used canceled
    > End Select
    > End If
    >
    > ThisWorkbook.SaveAs FileName:=Fname
    > End If
    > End Sub
    >
    > "Don Lloyd" <[email protected]> wrote in message
    > news:[email protected]...
    >> Hi Norman,
    >>
    >> I've tried your code as is.
    >> When the message saying that the file already exists shows and I choose
    >> not to replace it, the error 1004 appears, which is fatal if not handled.
    >>
    >> regards,
    >> Don
    >>
    >> "Norman Jones" <[email protected]> wrote in message
    >> news:%[email protected]...
    >>> Hi Don,
    >>>
    >>> Revising your posted nacro to reflect my suggestion, gives:
    >>>
    >>> Sub UserFileSave()
    >>> Dim Fname, Suggestion, Hdr
    >>> Suggestion = "MyFile " & Format(Date, "dd mmm yy")
    >>> Hdr = "Please choose a Location and Name then click Save."
    >>> Fname = Application.GetSaveAsFilename(Suggestion, _
    >>> fileFilter:="Excel File(*.xls), *.xls)", Title:=Hdr)
    >>> If Fname <> False Then
    >>> ThisWorkbook.SaveAs Filename:=Fname
    >>> Else
    >>> ' User Cancelled - dont save
    >>> End If
    >>> End Sub
    >>>
    >>> This did not error for me.
    >>>
    >>> ---
    >>> Regards,
    >>> Norman
    >>>
    >>>
    >>>
    >>> "Don Lloyd" <[email protected]> wrote in message
    >>> news:%[email protected]...
    >>>> Thank you Norman and Claud
    >>>>
    >>>> Unfortunately neither method works properly as the error keeps coming
    >>>> up when the offer to replace the file is declined.
    >>>>
    >>>> For the time being I've managed to overcome it with "unqualified" error
    >>>> trapping and then using recursion.
    >>>>
    >>>> Thanks for your time and effort.
    >>>>
    >>>> regards,
    >>>> Don
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1