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

Improve this question
5
  • 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 Commented Oct 21, 2017 at 16:35
  • You have an extra quote in the end of the line with v(1) Commented Oct 21, 2017 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. Commented Oct 21, 2017 at 16:58
  • why do you have 3 URLs when you do not do anything with the first two? Commented Oct 21, 2017 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. Commented Oct 21, 2017 at 21:14

1 Answer 1

Reset to default
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!

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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.
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.
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.