CODEWITHSUNDEEP
vbams-access
user3846155
user3846155

Reputation: 17

VBA Error handler with a GOTO and multiply errors

So I can't seem to get this to work and I do not understand why. It says error 53 but when I change the intNumber to a number that does not exist it works fine.

I believe the issue is having multiple errors happening at the same time.

Function Download_Location() As String
'kill the file as well
    Dim intNumber As Integer, strOther As String
    intNumber = 1
fReshstart:

    Download_Location = "C:\Users\" & Environ$("Username") & "\Downloads\MSaccessfile" & intNumber & ".xlsx"

    On Error GoTo Errhandler
Kill Download_Location


  Errhandler:

   Select Case Err

   Case 53: 'why wont it pick error 53
       Err.Clear
       MsgBox "it worked"
       Exit Function 'no file to delete

    Case 70: intNumber = intNumber + 1
       Err.Clear
       GoTo fReshstart

    End Select

End Function

Upvotes: 2

Views: 123

Answers (2)

user3846155
user3846155

Reputation: 17

Found the problem the issue was I needed to clear the error handler using On Error Goto -1 

Errhandler:

Select Case Err
Case 53: 'used when their is no file to kill
    On Error GoTo -1
Case 70: intNumber = intNumber + 1 'used when the file is opened already so new file is required to be built
   On Error GoTo -1
   GoTo fReshstart

End Select

End Function

Upvotes: 0

PractLogical
PractLogical

Reputation: 258

I've run into a lot of complicated error scenerios using Kill and created a nifty function to deal with it. Here it is for anyone that is interested. It's pretty simple but will help not having to have such insane error handling involving Kill. Hope it helps you and others out.

First Add this function to a/the module:

Function KillAndWasKilled(strFilePath As String) As Boolean
  'attempt to kill a file and report success; trying to head off all the oddball stuff that can happen in functions when kill is used
  On Error GoTo ErrHandler
  Kill strFilePath
  KillAndWasKilled = True
Exit Function
ErrHandler:
  KillAndWasKilled = False
End Function

Now Replace This:

Kill Download_Location

With This

if dir(Download_Location) <> "" then 'the file exists; if it doesnt exist then there's nothing to attempt to kill!
  if KillAndWasKilled(Download_Location) = false then 'something went wrong when trying to kill the file
     err.Raise -666, , "Unable to Delete " & Download_Location
  end if

end if

Upvotes: 1

Related Questions