Finding Element In HTML with VBA

Question

I have been trying to use VBA to make some work I have in Excel Easier, so far, it has been great.. But currently I have these 2 elements I have to grab out of a HTML document that I cant for the life of me figure out :

Firstly, here is my current code :

Enum READYSTATE
     READYSTATE_UNINITIALIZED = 0
     READYSTATE_LOADING = 1
     READYSTATE_LOADED = 2
     READYSTATE_INTERACTIVE = 3
     READYSTATE_COMPLETE = 4
End Enum

Public Sub GetData()

    Site = InputBox("Enter Website Link ", "Enter Product Link")

    Dim ie As InternetExplorer

    Dim html As HTMLDocument

    Set ie = New InternetExplorer
    ie.Visible = False
    ie.navigate Site

    Do While ie.READYSTATE <> READYSTATE_COMPLETE
    Application.StatusBar = "Trying to go to Product Page..."
    DoEvents
    Loop

    Set html = ie.document

    Set ie = Nothing
    Application.StatusBar = ""

    Dim Title As String
    Dim Description As String
    Dim Vendor As String
    Dim Image As String
    Dim PType As String    

    Vendor = ???
    Image = ???
    Title = html.getElementsByClassName("name")(0).innerText
    Description = html.getElementsByClassName("specs block")(0).outerHTML
    PType = html.getElementsByClassName("kind")(0).innerText

    Cells(ActiveCell.Row, 2) = Title
    Cells(ActiveCell.Row, 3) = Description
    Cells(ActiveCell.Row, 4) = Vendor
    Cells(ActiveCell.Row, 5) = PType


End Sub

What I'm looking for is that Vendor variable ( called "brand" below ) as well as the Image link, here is the snippet of HTML that displays the values :

  <meta itemprop="brand" content="Intel" />
  <meta itemprop="image" content="http://ecx.images-amazon.com/images/I/510BosCAMcL.jpg" />

The line's "content" is what im looking for.

Any help would be appreciated, thanks!

( PS. the HTML comes from this link : https://pcpartpicker.com/product/W67wrH/intel-cpu-bx80646g1820 )

Sorceri · Accepted Answer · 2016-12-09 19:47:11Z

1

Since you are trying to get all the meta elements you can loop through those and grab the brand and image by checking for the itemProp string

Edit: You appeared to have removed the metaElements line from your question.

Set metaElements = html.all.tags("meta")

Dim brandFound As Boolean
Dim hElement As IHTMLElement
brandFound = False
For Each hElement In metaElements
    If InStr(1, hElement.outerHTML, "itemprop=" & Chr(34) & "brand" & Chr(34)) <> 0 Then
        Vendor = hElement.Content
        brandFound = True
    End If
    If brandFound = True Then
        If InStr(1, hElement.outerHTML, "itemprop=" & Chr(34) & "image" & Chr(34)) <> 0 Then
            Image = hElement.Content
            Exit For
        End If
    End If
Next hElement

answered Dec 9, 2016 at 19:47

Sorceri

8,0932 gold badges32 silver badges40 bronze badges

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

1 Comment

Kazuto Kirigaya Over a year ago

Thanks! Ill have a look at this ( i removed the metaElemets as i was unsure if it would be used )

Collectives™ on Stack Overflow

Finding Element In HTML with VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related