Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Error handling? or possible overwrite file code

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have some code here that checks to see if a path exists, creates the path/dir if it doesn't exist, and then saves the active workbook in the dir. This works great, once. Problem comes in if the file already exists then Excel prompts the user asking if they want to overwrite. If the user says no, my VB errors out. Is there anyway to stop my VB from puking? Or my 2nd choice would be to stop excel from prompting and just automatically overwrite the file? Here is my current code:

    Private Sub savebutton_Click()
    Path = "c:worksheets"
    If PathExists(Path) = False Then MkDir "c:worksheets"

    ChDir "C:worksheets"
    ActiveWorkbook.saveas Filename:= _
    "C:worksheetsssworksheet_" & Worksheets("ssworksheet").Range("c6") & ".xls", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    End Sub

    Function PathExists(pname) As Boolean
    ' Returns TRUE if the path exists
    Dim x As String
    On Error Resume Next
    x = GetAttr(pname) And 0
    If Err = 0 Then PathExists = True _
    Else: PathExists = False

    End Function

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Amend code as follows;

    On Error Resume Next
    ActiveWorkbook.SaveAs Filename:= _
    "C:worksheetsssworksheet_" & Worksheets("ssworksheet").Range("c6") & ".xls", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    Err.Clear


    Unless you want to see the error and respond to it then;

    On Error Resume Next
    ActiveWorkbook.SaveAs Filename:= _
    "C:worksheetsssworksheet_" & Worksheets("ssworksheet").Range("c6") & ".xls", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False

    If Err Then MsgBox Err.Number & ":=" & Err.Description, _
    vbMsgBoxHelpButton, "Error", Err.HelpFile, Err.HelpContext



    HTH

    Ivan

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi keith


    Ivan the terrible has already answered you question, the only poit I would add is, you should use:

    On Error GoTo 0 to reset error trapping.

    Also if you decide to go with your send choice:

    Application.DisplayAlerts=False
    'You code
    Application.DisplayAlerts=True

    This will force Excel to go with the fault answer for any warnings.



  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-29 16:35, Dave Hawley wrote:
    Hi keith


    Ivan the terrible has already answered you question, the only poit I would add is, you should use:

    On Error GoTo 0 to reset error trapping.

    Also if you decide to go with your send choice:

    Application.DisplayAlerts=False
    'You code
    Application.DisplayAlerts=True

    This will force Excel to go with the fault answer for any warnings.


    Thanks Dave .....As Dave has pointed out
    reset the Error call...good practice even
    if you don't need it.

    cheers Dave

    Ivan

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks guys. It worked great.

    I had seen the "On Error Resume Next" code in other posts of various types and thought it might be the answer, but I wasn't sure exactly what it did (or how to turn it back on/reset it).

    I was scared of it because the 'resume next' implies it might skip over the current line of code becaused it got an error. And I didn't want to risk the file not getting saved.

    Thanks to you guys I think I now understand how it works..

    Keith

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    one extra tip i find useful,

    it's often worth isolating an operation like this where you use ON ERROR to trap likely errors, so that it doesn't RESUME NEXT on other items in a larger procedure.

    You can put just these three or four lines of code in its own subroutine with the on error command.

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    gotcha - thanks

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave = Ivan

    Guys a big tip o the hat

    I never knew that or would i.. ermm sounds like standard from now on regardless... UNLESS i need next regardless.

    Good men!
    you friend Jack
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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