Extracting Data from Webpage

[The Gunslinger]

should be easy, i have similar code running in the same workkbook for exactly the same task, but for different data from different page structures, but i can't get this one to work right, i've basically mullered my code so i have nothing sensible to post as a broken example. i'm constantly getting different object related errors that are quite frankly f***ing annoying as the supposed help on these is useless to diagnose the issue

From this page....

https://www.youinvest.co.uk/market-research/LSE:GWMO
/code]

i want to extract the ISIN number, which is in this line of the html
[code]
<td id="Col0Isin">IE00B1FR8863</td>

the "IE00B1FR8863" value is the innerText or innerHTML, it shouldn't be so hard, but it's killing me! i've been trying to use getElementById and that's obviously not working !

i'm trying not to loop through all the <tr> elements on the page, to get to that single unique item

can anyone help please ?

 

[John_w]

Try this:

Public Sub IE_Test()
   
    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .navigate "https://www.youinvest.co.uk/market-research/LSE:GWMO"
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
        MsgBox .Document.getElementById("Col0Isin").innerText
    End With
    
End Sub

 

[The Gunslinger]

ok that's really frustrating because that is not a million miles from where i was and it works, only differences i see is that i pause the code till it's loaded, so no doevents, and i used getobject instead of create, and referenced it directly rather than in the with statement

and i was creating the object differently, and opening hidden, so need to remember to close it properly etc

here's what i had

Sub Get_ISIN(Market As String, StkCode As String, ISIN_Cell_Ref As Range)

    'On Error GoTo Leave
    
    Dim ISIN_Data As String
    Dim ISIN_Cell As Range
    Dim Chr_Strt As Long
    Dim Chr_End As Long
    Dim LoadTime As Date
    Dim IE As Object

    Set IE = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}")
    IE.Visible = False      ' Hide instance of IE
    
    ' Clear data value before starting
    ISIN_Data = ""
    
    ' Set ISIN_Cell to Required Cell Address using reference passed on calling the routine
    Set ISIN_Cell = Range("$AL" & ISIN_Cell_Ref.Row)
    
    ' Strip StkCode text to valid EPIC code removing ".L" or "LON:" if found
    Chr_Strt = InStr(Trim(StkCode), ":") + 1            'find : in epic text (need the +1 to remove the : character from the return if found)
    Chr_End = InStr(Trim(StkCode), ".") - 1    'find . in epic text (need the -1 to remove the . character from the return if found)
    If Chr_End <= 0 Then Chr_End = Len(Trim(StkCode))      'set last character as the length of the epic string if "." not found
    StkCode = Mid(Trim(StkCode), Chr_Strt, Chr_End - (Chr_Strt - 1))
    
    ' Set LoadTime variable (time page is called + 6 seconds, to allow exit on hung pages)
    LoadTime = Now() + TimeValue("0:00:06")
    
    ' Load page in hidden IE instance using passed market and stock code
    IE.Navigate "https://www.youinvest.co.uk/market-research/" & Market & ":" & StkCode
    
    ' Wait while page loads in the hidden IE browser instance
    Do While (IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE)
        ' Check how much time has passed while in the loop skip out at 6 seconds
        If Now() > LoadTime Then
            Exit Do
        Else
            ' Adjust status bar text to display current EPIC being examined
            Application.StatusBar = "Looking up ISIN/SEDOL Data for  " & StkCode & "  ......Please wait"
        End If
    Loop
        
    ' Load Element "Col0Isin" (ISIN No.) and check for value
    ISIN_Data = IE.Document.getElementById("Col0Isin").innerText
    If ISIN_Data = "" Then GoTo Leave ' Exit if not found or empty, either Invalid StkCode(EPIC) or no ISIN listed.
    
    ' Transfer ISIN Data to worksheet cell
    ISIN_Cell.Value = Trim(ISIN_Data)

Leave:
    ' Clean up IE instance created.
    IE.Quit
    Set IE = Nothing
    
    ' Reset Status Bar Text
    Application.StatusBar = " "
    
    ' Reset Error Trapping
    On Error GoTo 0

End Sub

My code above, gives me a "Runtime Error 424 object required" on this line

ISIN_Data = IE.Document.getElementById("Col0Isin").innerText

As i see it not a major difference, but obviously enough, so i'll adjust it for your code

thanks

 

[The Gunslinger]

ok i give up for tonight, it's too late for my brain, i can't get that to integrate into my routine, i'm constantly getting the same runtime error and it's p1ssing me off, so screw it for tonight

 

[The Gunslinger]

well now i am completely baffled, thought i'd come back at this this morning and try and find out why my code fails.

It seems that it's loosing the reference to "IE" being an object, so i changed the Dim line form object to webbrowser and it worked, i also had a DoEvents: in the loop code, which i then took out and all seemed ok,
so then i went to the other routine that used similar code, switched that to webbrowser form object, and tht too worked, then when i went back to the other one it was broken

so i'm stuck at this point, my code fails, your example works, but i can't get it to work in my environemnt