Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving & Error Control

    Greetings!

    I am using the following code to save my worksheet from a userform:

    sub cmdbutton_click()
    ActiveWorkbook.SaveAs "today.xls"
    end sub

    What can I add to this to prevent errors when the user cancels out of the save?

    Thanks!
    Drk
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving & Error Control

    Drk,

    If the file did not already exist, your user would not get a message, and would not get a chance to cancel out.

    If that's the cou might try to delete the file before performing the SavesAs
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving & Error Control

    the thing is, it's saved, then e-mailed automatically to me. They will send me the file once a day, the file contains statistics of performance for that day. I'm not familiar with programming, but aren't there errorlevel codes or something I can add to bypass a collapse of the code?

    Thanks!
    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving & Error Control

    perhaps there is a way to rename the existing file to yesterday's date? or save the current file as today's date?

    Thanks again!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving & Error Control

    Drk

    Start with this (you may need to have a reference in your project to scripting- Tools, References, select "Microsoft Runtime scripting" from the list):

    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject")

    If fs.FileExists("c:times.xls") Then
    fs.DeleteFile ("c:times.xls")
    End If

    There's also CopyFile and MoveFile methods which may help you
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving & Error Control

    This option is in Excel '97, correct?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving & Error Control

    Drk,

    Sorry, possibly not.

    If you have the reference to the "Microsoft scripting runtime" you'll be able to use this method; otherwise you will need to look at another method. Have a look at for a discussion on scripting

    Do you have the reference?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving & Error Control

    Apparently not, there is TOOLS, ADD-INS... it seems it would be there, but there's no reference to Microsoft Scripting Runtime. Bummer too, I have 2k at home... [img]/w3timages/icons/laugh.gif[/img]

    As for the discussion, looks like there may be a helpful item or two there, the msgbox components look promising, I will post back if they don't work.

    Thanks Millions!
    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving & Error Control

    Okay,

    Heres what i've got!
    '******************CODE START***************
    Dim strExists
    strExists = Dir("C:WINDOWSTEMPtoday.xls", vbDirectory)

    If Len(strExists) > 0 Then
    MsgBox "Are you sure you want to finalize?", vbOKCancel, "Verify Finalization"
    If vbCancel Then '???????????
    MsgBox "Please Overwrite Existing File", vbOKOnly, "File Already Exists."
    Else
    End If
    ActiveWorkbook.SaveAs FileName:="C:WINDOWSTEMPtoday.xls", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    '*************CODE END************
    The problem I am having is how do I step over the save command if the user clicks on Cancel? how do I abort the sub?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  10. #10
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving & Error Control

    To abort the sub, it depends on where you've got the routine.

    If it's just a button which gets clicked, then add an "exit sub" statment.

    If it's in an AutoClose, I'm not sure if the close can be stopped. You would be better in that situation to put the code into the "Workbook_BeforeClose", then set "cancel = true" before you exit the sub
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving & Error Control

    I've added an exit sub statement, in the following syntax:

    MsgBox "Are you sure you want to submit your call statistics for today?", vbOKCancel, "Verify Finalization"
    If vbCancel Then Exit Sub

    But, even when I click on OK now, it's not proceeding... is this correct to specify that only if I click "Cancel" it's to end the sub?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving & Error Control

    I've added an exit sub statement, in the following syntax:
    '****CODE START******
    ...
    MsgBox "Are you sure you want to submit your call statistics for today?", vbOKCancel, "Verify Finalization"
    If vbCancel Then Exit Sub
    ...
    '********CODE END*********

    But, even when I click on OK now, it's not proceeding... is this correct syntax to specify that only if I click "Cancel" it's to end the sub?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  13. #13
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving & Error Control

    Try the cancel in this way (also note declaration of "strExists" as a string- otherwise it is a less efficient variant):


    Dim strExists As String
    strExists = Dir("C:WINDOWSTEMPtoday.xls", vbDirectory)

    If Len(strExists) > 0 Then
    If MsgBox("Are you sure you want to finalize?", vbOKCancel, "Verify Finalization") = vbCancel Then
    Exit Sub
    MsgBox "Please Overwrite Existing File", vbOKOnly, "File Already Exists."
    Else
    MsgBox "save"
    End If
    ActiveWorkbook.SaveAs Filename:="C:WINDOWSTEMPtoday.xls", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    End If
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

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

    Re: Saving & Error Control

    What do you want to happen when you do the SaveAs? Do you want the user to see the dialog boxes and be able to cancel, or do you want to just save the file and go on? If you just want to save the file (replacing any file with the same name), then you can do something like this:

    <pre>sub cmdbutton_click()
    Application.Displayalerts = False
    ActiveWorkbook.SaveAs "today.xls"
    Application.Displayalerts = True
    end sub
    </pre>

    Legare Coleman

  15. #15
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving & Error Control

    Brilliant! Through a combination of the two, I have a seamless transaction, which still alerts the user, Marvelous!

    Thank You!!!!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Posting Permissions

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