Question on "On Error GoTo skip"

[dan]

I have this routine in "TestBook.xls"


Sub check()
' I want to check if "data.csv" is open.
' Why statement "On Error GoTo skip1" works once only?
' I expected Range("L4:L10") = 0 if "data.csv" is not open
' Range("L4:L10") = 1 if "data.csv" is open
' Please help

Dim r As Long
For r = 4 To 10
On Error GoTo skip1
Windows("data.csv").Activate
GoTo skip2
skip1:
Windows("TestBook.xls").Activate
Cells(r, 12) = 0
GoTo skip3
skip2:
Windows("TestBook.xls").Activate
Cells(r, 12) = 1
skip3:
Next r
End Sub

 

[Sandy Mann]

I assume it is because you are using an object. Help says:

"On Error Resume Next Specifies that when a run-time error occurs, control
goes to the statement immediately following the statement where the error
occurred where execution continues. Use this form rather than On Error GoTo
when accessing objects."

And this seems to work:

Sub check()
' I want to check if "data.csv" is open.
' Why statement "On Error GoTo skip1" works once only?
' I expected Range("L4:L10") = 0 if "data.csv" is not open
' Range("L4:L10") = 1 if "data.csv" is open
' Please help

Dim r As Long
For r = 4 To 10
On Error Resume Next
Windows("data.csv").Activate
If Error = Error(9) Then GoTo skip1
GoTo skip2
skip1:
Windows("TestBook.xls").Activate
Cells(r, 12) = 0
GoTo skip3
skip2:
Windows("TestBook.xls").Activate
Cells(r, 12) = 1
skip3:
Next r
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[dan]

Great Help!
Thank you very much, Sandy

I assume it is because you are using an object. Help says:

"On Error Resume Next Specifies that when a run-time error occurs, control
goes to the statement immediately following the statement where the error
occurred where execution continues. Use this form rather than On Error
GoTo
when accessing objects."

And this seems to work:

Sub check()
' I want to check if "data.csv" is open.
' Why statement "On Error GoTo skip1" works once only?
' I expected Range("L4:L10") = 0 if "data.csv" is not open
' Range("L4:L10") = 1 if "data.csv" is open
' Please help

Dim r As Long
For r = 4 To 10
On Error Resume Next
Windows("data.csv").Activate
If Error = Error(9) Then GoTo skip1
GoTo skip2
skip1:
Windows("TestBook.xls").Activate
Cells(r, 12) = 0
GoTo skip3
skip2:
Windows("TestBook.xls").Activate
Cells(r, 12) = 1
skip3:
Next r
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk