excelvbahref

VBA: get a value inside the href-tag


I'm quite a beginner with VBA and have a question regarding href-tags.

In following example I have the href-tag to identify my requested value, which is inside in the b-tag, in this case the 585.00. That's the value I need.

<div style="float: left; width: 72px; text-align: right; height: 15px; padding: 2px; background-image: url(/img/rowbgnd.png)">

<a href="/goldvreneli-20-franken-vreneli/de/44-1/add/CH-GV-20-1897.2-2/1" title="in den Warenkorb legen">

<b>585.00</b>
</a>
</div>

I didn't find any solution. Can anyone help me?

To get a value from a website I found this article Trying to extract ONE value from a webpage with VBA in Excel and it worked fine for another case. But in the example above I don't have a class for which I could use this code:

IE.Document.getElementsByClassName("className")

I found some good Entries (e.g. VBA return html element href from class table) to get the value of the href itself, but nowhere, how to get the tag below.


Solution

  • Try this;

    Sub Test()
        Dim HTTP As Object, HTML As Object
        Dim URL As String, objColl As Object, i As Long
        
        URL = "https://schweizergeld.com/goldvreneli-20-franken-vreneli/de/44-1"
        
        Set HTTP = CreateObject("MSXML2.XMLHTTP")
        Set HTML = CreateObject("HTMLFILE")
        
        HTTP.Open "GET", URL, False
        HTTP.send
        
        If HTTP.Status = 200 Then
            HTML.body.innerHTML = HTTP.responseText
            Set objColl = HTML.getElementsByTagName("A")
            
            For i = 0 To objColl.Length - 1
                If objColl(i).HREF = "about:/goldvreneli-20-franken-vreneli/de/44-1/add/CH-GV-20-1897.2-2/1" Then
                    MsgBox objColl(i).innerText
                    Exit For
                End If
            Next
        Else
           MsgBox HTTP.StatusText
        End If
        
        Set HTML = Nothing
        Set HTTP = Nothing
    End Sub