CODEWITHSUNDEEP
excelvbaparsing
sammy_boy
sammy_boy

Reputation: 13

Parsing HTML with Excel VBA

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

Upvotes: 1

Views: 743

Answers (1)

WAR
WAR

Reputation: 129

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!

Upvotes: 1

Related Questions