1

Hi StackOverflow community!

Before I go to the nearest petrol station to buy a gallon of petrol and then proceed to a shopping centre (wiping tears of desperation along the way) to set my self on fire...

I am trying to parse few websites, each an instance of finance.google.co.uk, for share prices. This sub only opens 3 sites(for now, I'm planning few hundred), get the share price from each and puts it in cells A1, A2, A3 respectively. The code I wrote works fine only after starting/ restarting my laptop and running excel the first time. 2nd and subsequent runs produce random results, meaning 2nd run will give me say A1 and A3 values, next A1, next A1 and A2 etc. Have spent last few days trying to figure out what is going on. Also I cant find a way to get rid of the "On Error Resume Next" line. If I do that I get "Method 'Navigate' of Object 'IWebBrowser2' failed" error, any idea why?. BTW, I'm green, code for personal use, or trying to. So maybe I overlooked something painfully simple, or what I think is the case, simply don't know that simple thing.

The gear:
- windows7 32bit
- IE 11

My sub:

    Sub Google_Finance()

    Dim o(3) As String
    o(1) = "http://finance.google.co.uk/finance?q=LON%3ABARC"
    o(2) = "http://finance.google.co.uk/finance?q=LON%3ACCH"
    o(3) = "http://finance.google.co.uk/finance?q=LON%3ASUK2"

    Dim IE As Object
    Set IE = New InternetExplorer
    IE.Visible = False

    For i = 1 To 3

    IE.navigate o(i)
    On Error Resume Next

    Do While IE.Busy
    DoEvents
    Loop

    Next i

    Dim n as Integer
    n = 1

    Dim v(3) As Variant
    v(1) = IE.document.getElementById("ref_11248216_l").innerText"
    v(2) = IE.document.getElementById("ref_243113080920948_l").innerText
    v(3) = IE.document.getElementById("ref_14572000_l").innerText

    For i = 1 To 3

    Sheet1.Range("a" & n) = v(i)
    n = n + 1

    Next i

    IE.Quit

    Set IE = Nothing

    End Sub

Cheers,

Sam

sammy_boy
  • 13
  • 2
  • Why do you say you can't remove the `on error resume next` line? Couldn't you just remove it? Also, perhaps it's just here, but you have an errant `"` after `v(1)`...Also, perhaps your serrinv of `v()` should be within your first `For i` loop? After you navigate to `o(3)`, I believe that is the page that will be open for the `v()`s...no – BruceWayne Oct 21 '17 at 16:35
  • You have an extra quote in the end of the line with `v(1)` – Michael Oct 21 '17 at 16:45
  • Hi Bruce, as I wrote if I remove "On error..." i get a runtime error. The " is just a typo. If I put the v() stuff inside first loop, it will start overwrite it every time it navigates to next page and as I wrote I plan to open hundred pages, so time is a factor, as in the end it will be refreshed every...dunno yet. – sammy_boy Oct 21 '17 at 16:58
  • why do you have 3 URLs when you do not do anything with the first two? – jsotola Oct 21 '17 at 20:05
  • Hi jsotola, thank you for pointing it to me. As I mentioned in my question, I am as green as the grass in your back garden. Changed the sub: deleted all urls apart from first and got rid of the loop(now unnecessary) and it works. Beats me how but it does. – sammy_boy Oct 21 '17 at 21:14

1 Answers1

1

I tried running the code on my side. I don't get the error you are getting when removing 'on error'. Code works OK for me few times in a row, no issues occurred.

To me it looks like this: the error you are getting is happening when IE is trying to reach the page. Because you use 'on error resume next', the compiler does not try to rerun this task on failure, thus any error leads to no data for the value it is trying to read from the web.

You should either: a) remove 'on error resume next' or b) change error handling to loop the task until completion.

For solution a) you will need to overcome your error, which I believe is explained here

For solution b) you will need to change your sub to include a loop - example solution can be found here

Hope this helps!

WAR
  • 129
  • 1
  • 1
  • 8
  • Hi WAR, Thanks for your help, worked magic!!. I went for option b. Will also explore option a later as it will need further reading than just adding "Medium" to InternetExplorer. A big CHEERS. – sammy_boy Oct 21 '17 at 18:01
  • Glad it worked! I'd appreciate marking the question as answered :-) As for the IE error, it is clearly an issue basing on your setup so IE looks like the guilty one here, as the code is OK. Maybe some references in Excel VBA are missing, but I don't think that should be the case. – WAR Oct 21 '17 at 18:11
  • Yup, its always that Microsoft at fault. Will have to look into it, though the sheer thought of it makes me ***censored***. I actually had to use the error handler twice. Once to wrap it around IE.navigate o(i) and around the v() stuff. – sammy_boy Oct 21 '17 at 18:31