0

I wrote a code to scrape web information from Google Patents in VBA which works pretty well but unfortunately it is pretty susceptible to two errors.

The two most common are

  • runtime error
  • Windows OLE is waiting for ...

All that needs to happen when this occurs is just press ok, ctrl break, F5 and execute again. Also for reasons unknown to me the code seems to slow down after x minutes and then breaking and restarting it accelerates the speed.

So as a patch-all solution I'd like to do the following. After x minutes (or even better after let's say 40 iterations), automatically break the execution and start again. A second error-handling way which would be great is do something like

If Range("A1".End(xlDown)) at time t = Range("A1".End(xlDown)) at time t + 600 Then restart code execution Else continue code execution

I'm basically hoping to automate my error handling so that I can run the code overnight and sleep like a baby :)

Community
  • 1
  • 1
SJDS
  • 1,239
  • 1
  • 16
  • 31
  • This doesn't sound like a good idea to me. Have you tried On Error Resume Next? – rex May 28 '14 at 11:25
  • I've posted an answer but I'm not sure how much it will help you; it might set you on the right track to a solution. PS: Imperial graduate right here ;) – rex May 28 '14 at 12:53
  • Thanks @ArmenSafieh-Garabedian . Good uni for sure :) – SJDS May 28 '14 at 13:11
  • 2
    You need to design your code in such a way to handle this better. Make each iteration a function call and add actual error handling (on error resume next is NOT error handling). Then you can simply skip over the "bad" internet explorer calls and avoid the entire code breaking. – enderland May 28 '14 at 13:22
  • Also see this function here - http://stackoverflow.com/a/21338071/1048539 - it might be very helpful – enderland May 28 '14 at 13:23
  • I'll bet if you rewrote it using xmlhttp rather than automating Internet Explorer, you could just eliminate the problems. You should consider posting your code on the Code Review StackExchange site. – Dick Kusleika May 28 '14 at 14:02

1 Answers1

1

I do not think there is a code statement in Excel VBA that allows you to programmatically break code and then continue. However, you can simulate pressing of the Break button using VBA, but I don't know how you can then ask the code to press the Continue button since the code will have paused already...

Application.VBE.CommandBars.FindControl(ID:=189).Execute 'press Break button
DoEvents

The below should press the Continue button, but I couldn't get it to work - perhaps someone knows how?

Application.VBE.CommandBars.FindControl(ID:=186)

Alternatively, you may try letting your routine go to sleep, to wait for IE to respond, in which case you'll need to expose some windows APIs as follows.

In module code, at the very top, you need to put the Declare statement.

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

And then you can use Sleep() like so anywhere:

Sub Test()
    Debug.Print "I will now sleep for 1000 milliseconds...."
    Sleep(1000)
    Debug.Print "I am now awake."
End Sub
rex
  • 3,133
  • 6
  • 35
  • 62
  • Hey, that sleep idea sounds like an interesting approach. I guess I could use that conditional on specific values as well. To basically randomly slow down the process. But for ie to respond, I currently use `Do DoEvents If Err.Number <> 0 Then ie.Quit Set ie = Nothing GoTo the_start: End If Loop Until ie.ReadyState = 4' – SJDS May 28 '14 at 13:18
  • Sorry, I'm trying to implement this to try it out but do I store this under Modules or under forms? And where to put the `Declare...` line. When I copy paste it I get an error "only comments may appear after End Sub, End Function, or End Property" ... – SJDS May 28 '14 at 13:24
  • To be honest I've never used IE with VBA. It's hard to tell if your snippet is working properly without the rest of the code but it kind of makes sense I guess to quit IE and restart it if it throws an error. – rex May 28 '14 at 13:25
  • Thanks a lot, the sleep statement works perfect now and it eases the pressure on the ie object to move as quickly. Great, absolutely great! Thanks a lot. If you don't use ie, which browser do you use (if there are alternatives readily available I'd happily switch :) – SJDS May 28 '14 at 13:45
  • Glad to help - I haven't had to use a browser before but I have heard of something called Selenium Wrapper which let's you use Chrome and FireFox – rex May 28 '14 at 13:53
  • Good recommendation on `Sleep` API call. @simon_icl you should be putting the `Sleep 1000` call *inside* the `Do` loop while you're waiting for `IE.readyState = 4`. – David Zemens May 28 '14 at 13:59
  • Exactly what I did David. It has reduced the errors a lot but not eliminated them. ie still runs into runtime errors but ok, it's a vast improvement. Will check out Selenium Wrapper as well. Thanks so much guys! – SJDS May 28 '14 at 14:39