CODEWITHSUNDEEP
vbaexcel
Ans
Ans

Reputation: 1234

Excel vba On Error GoTo different handlers, depending on an error

I have excel vba code that opens different files on makes use of them. An error can occur because there is no file where excel loos for it. I want to create a MsgBox on such errors with a message which specific file is absent.

Now I can only

On Error GoTo ErrorHandler

ErrorHandler:
     MsgBox("File is absent")

But I can't specify which exactly file is absent. Is there a way to achieve it through error handler? Maybe through some additional variable?

EDIT: I open files through

Workbooks.Open Filename:=...

But I'm curious about what one should do if the case is

Dim fileTitle As String
filetitle=Dir()

as well.

Upvotes: 0

Views: 1572

Answers (4)

David Zemens
David Zemens

Reputation: 53653

Rather than hard-coding the file path via:

Workbooks.Open Filename:=...

Use a variable to represent the file path/name:

Dim fileName As String
fileName = "C:/path/to/my/file.xlsx"

Then, check to make sure it exists before you attempt to open it:

If FileIsAccessible(fileName) Then
    ' Do stuff
Else
    MsgBox fileName & " doesn't exist or cannot be opened"
    Exit Sub
End If

Use a custom function like

Function FileIsAccessible(path$) As Boolean
    Dim FF As Long
    On Error GoTo EarlyExit
    FF = FreeFile

    'Does file exist?
    '  Raises Error 53 if file not found
    Open path For Input Access Read As FF
    Close FF

    'If file exist, is it accessible?
    '  Raises error 70 if file is locked/in-use
    FF = FreeFile
    Open path For Binary Access Write As FF
    Close FF

EarlyExit:
FileIsAccessible = (Err.Number = 0)
End Function

Upvotes: 3

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

You still have access to your variables in error handler, so you know within which file error happen:

Sub ...
    Dim filename As String
    On Error GoTo ErrorHandler
    filename = Dir(...)
    While filename>""
        Set wb = Workbooks.Open(filename)
        ...
        filename=Dir
    Wend
    Exit Sub
ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & " File: " & filename
End Sub

Upvotes: 2

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Give this a try and tweak it as per your requirement. This will give you a starting point to deal with error handling....

Assuming you are trying to open a file abc.xlsx which is located at your Desktop and if this file isn't found on Desktop, the error handling will be triggered.

Don't forget to use Exit Sub before Error Handling label so that it is not executed if the file was found.

Dim wb As Workbook
Dim FilePath As String

FilePath = Environ("UserProfile") & "\Desktop\abc.xlsx"

On Error GoTo ErrorHandler
Set wb = Workbooks.Open(FilePath)
'Other stuff here if file was found and opened successfully
'
'
'
'
Exit Sub
ErrorHandler:
     MsgBox Err.Number & vbNewLine & Err.Description, vbCritical, "File Not Found!"

Upvotes: 1

RGA
RGA

Reputation: 2607

Two ways to go about this. First, as you suggested, (which is also the easier of the two), you can make a variable that will carry the file name that you reassign after each file successfully loads. That name can then be passed into your message box in the event of a failure. If all you need to do is pass this name, this is the better solution.

The second option would be to create multiple error handlers. I would only recommend this if you need more customization with regard to how the error is handled, like wanting a different message to be displayed based on which type of file was missing. This option would make your code a good bit messier (as you would need to reassign the On Error GoTo ... statement multiple times, but its worth considering if you need a more complex solution.

Upvotes: 1

Related Questions