On Error Statements in VBA

[Lino]

Hello,

I am using the following code:

'For Each Cell in Range(A1:A10)
Cell.value = ThisValue
On Error GoTo ReadNextCell
Workbook.open....ThisValue
On Error GoTo 0

ReadNextCell:
Next Cell

What is happening is that when ThisValue doesn't exist in the opening destination folder it should GoTo ReadNextCell and resume at Next Cell.

This script works the first time but the next time it happens I get a run time error telling me that the file I am trying to open does not exist.

Why does this only work the first time...it appears it is ignoring the On Error GoTo ReadNextCell statement...Why?

Thanks,

Lino

 

[Swamp Thing]

http://www.mrexcel.com/board/viewtopic.php?topic=18863&forum=2&start=10

 

[Lino]

I tried the code on the attached link...however unless I use an exit sub command before the error handling code it still does not work propertly.

With the recommended code the error handling code runs even if there isn't an error which generates another runtime error "20"..."Resume Without Error"

Because it is a For Loop I do not want to use exit sub because then it will exit the loop and this is not my intention.

I am simply trying to tell the system...if the file does not exist skip it and go to the next cell...see what the value is...try to open the file...if it doesn't exist again move on to the next cell.

Thanks,

Lino

 

[Richie(UK)]

Hi Lino,

Have you tried On Error Resume Next ?

<pre>Sub test()
Dim ThisValue As String, Cell As Range

For Each Cell In Range("A1:A10")
ThisValue = Cell.Value
On Error Resume Next
Workbooks.Open ThisValue
On Error GoTo 0
Next Cell

End Sub</pre>
HTH

PS. I swapped the ThisValue to the start of the line as I didn't follow the logic the other way around. Let me know if I'm missing something.

 

[Lino]

On Error Resume Next only brings me to the next line of code...that is no good because if the "ThisValue" which is a filename can not be opened the rest of the code can't realistically run.

It needs to loop to the next value in the next cell

 

[Richie(UK)]

Hi Lino,

I think the code I gave you achieves what you are trying to do; if the workbook can't be opened the loop moves to the next workbook reference.

Anyway, looking back at your post further up you say that you can't get the error-handling routine to work unless you place an 'Exit Sub' before it. That's exactly what you are supposed to do - to stop it running as the last part of your main routine. (Have a look at the example under 'Error object' in the VBE Help). It should, however, be at the end of the sub and not within a loop. You just include a Resume Next statement in the error-handling to return execution to the loop.

HTH

_________________<font color="blue"> «««<font color="red">¤<font color="blue"><font size=+1>Richie</font><font color="red">¤<font color="blue"> »»»</font>

</gif>
This message was edited by Richie(UK) on 2002-11-05 10:17

 

[Lino]

I don't think your code achieves what i need it to do...after On Error GoTo 0 I have a lot more code to run through. The Next Cell doesn't appear until just before End Sub. So On Error Resume Next will jump to the next line which is On Error GoTo 0 then it will go the next line of code and so on until it gets to Next Cell.

What I said about the exit sub is that if I don't use it the code will run even if there isn't an error so if I use the Exit sub it will stop the procedure before it runs the error handling code...this is no good because then it exits the entire For Each Loop and I don't want this to happen what would be the point?

 

[Richie(UK)]

Just to save any further confusion, for me or anybody else, how about posting the whole routine so we get the whole picture?

 

[Russell Hauf]

How about checking for the file's existence first?

Something like this:

    For Each Cell In Range("A1:A10")
        
        If Dir(ThisValue)<> "" Then
        
            Cell.Value = ThisValue
            Workbooks.Open ThisValue
            ' Other code here
        End If
    Next Cell

So you just put everything inside of an if statememt...

If you have a certain directory that you are searching in then you just do something like this:<pre><font color='#000000'>
Dir("C:\" & ThisValue)</font></pre>

or<pre><font color='#000000'>
Dir(ThisPath & ThisValue)</font></pre>

if you use the latter, make sure your path has a backslash at the end.

HTH,

Russell
This message was edited by Russell Hauf on 2002-11-05 11:28

 

[IANW]

if you want to keep it similar to your existing code you could just put the actions that you want to perform in an if statement with a simple error check

Sub errorcheck()
For Each cell In Range("a4:a10")

ThisValue = cell.Value
x = 0
On Error GoTo collect
Workbooks.Open (ThisValue)
On Error GoTo 0
If x = 1 Then
'insert code here for workbooks that do open
End If


Next cell
'insert more code here if there is any
'
Exit Sub
collect:
x = 1
Resume Next
End Sub