CODEWITHSUNDEEP
vbaexcel
Vinnie
Vinnie

Reputation: 553

Error handling with if...then VBA

Is there any way to use if...then for error handling (without On Error... or GoTo!!!!)?

I have the below code, but it's stuck at the if statement.

Sub test()

            If IsError(Workbooks.Open("C:\Users\Desktop\Test\journals.xlsx")) = True Then

                'Do something

            End If

  End Sub

Thanks!

Upvotes: 0

Views: 15757

Answers (3)

luke_t
luke_t

Reputation: 2985

You can turn off error handling and then check if an error number was generated from the attempt at opening the workbook.

Dim wb As Workbook

On Error Resume Next

Set wb = Workbooks.Open("C:\Users\Desktop\Test\journals.xlsx")

If Err.Number > 0 Then

    '' there was an error with opening the workbook

End If

On Error GoTo 0

Edit 1: Since you are already doing so nicely, directly setting it to a wb object, why not use it's capabilities ? 

If wb Is Nothing Then

Upvotes: 3

Absinthe
Absinthe

Reputation: 3391

The simplest answer is no, it's expected that you'll use On Error in some fashion, either:

On error resume next
Workbooks.Open("C:\Users\Desktop\Test\journals.xlsx")
If Err.Number <> 0 then
' the workbook is not at that location
Err.Clear
On Error Goto 0
End If

or in a traditional error handler:

errhandler:

If Err.Number <> 0 then
    If Err.Number = 1004 Then
        ' the workbook is not at that location, do something about it, then resume next
    End If
End If

However, you can use the FileSystemObject to test if a file exists:

Dim fso As Object

Set fso = CreateObject("Scripting.FileSystemObject")
fileExists = fso.fileExists("C:\Users\Desktop\Test\journals.xlsx")

Upvotes: 2

user3598756
user3598756

Reputation: 29421

you could use Dir() function

If Dir("C:\Users\Desktop\Test\journals.xlsx") = "" Then
    'do something
Else
    Workbooks.Open "C:\Users\Desktop\Test\journals.xlsx"
End If

Upvotes: 4

Related Questions