Scraping from VBA (very close to working!!)

Question

I have been playing around with a scraper for consumer stocks and I can scrape data from the main page of items but once i start using the second, thid parges,

Sub asosdesc2()

 Const READYSTATE_COMPLETE = 4

Dim j As Integer

Dim ie As InternetExplorer
Dim Doc As IHTMLDocument
Dim xcolElements As IHTMLElementCollection
Dim ell As IHTMLElement

Dim pn As Integer

j = 1
Set ie = CreateObject("InternetExplorer.Application")

For pn = 1 To 1

    ie.Visible = False
    ie.Navigate "http://www.asos.com/Men/Sale/Accessories/Cat/pgecategory.aspx?cid=2097&CTARef=shop|sale|cat|accessories#parentID=-1&pge=0&pgeSize=36&sort=-1"

    Do
      DoEvents
    Loop Until Not ie.Busy And ie.readyState = READYSTATE_COMPLETE

    Set Doc = ie.Document

    'Set xcolElements = Doc.getElementsByClassName("description")
    'Here lies the problem. Description works perflectly, price doesn't
    Set xcolElements = Doc.getElementsByClassName("desc")



    For Each ell In xcolElements
      On Error GoTo Skip
      Range("B" & j).Value = ell.innerText
      j = j + 1
Skip:
    Next ell

On Error GoTo 0

Next pn

ie.Quit
Set el = Nothing
Set xcolElements = Nothing
Set Doc = Nothing
Set ie = Nothing



End Sub

I am trying to return the description, the price and the HREF code to identify the item

You probably need to clear the error handler with `On Error GoTo 0` after the `Skip:` line. Aside from that, it's not very clear what you're asking. Consider revising your question and providing more detail about how the observed results of this macro differ from your expected/desired results. — David Zemens, Feb 03 '15 at 16:27

score 0 · Answer 1 · answered Feb 19 '15 at 10:34

Instead of looping through the collection of "desc" anchors (which only contain the product description), I suggest you loop through their parent "items" collection. That way you can reference your anchors' siblings, which contain price etc.:

Sub asosdesc2()

    Const READYSTATE_COMPLETE = 4

    Dim j As Integer

    Dim ie As InternetExplorer
    Dim Doc As IHTMLDocument
    Dim itemsColl As IHTMLElementCollection
    Dim itemsEle As IHTMLElement

    Dim itemsAttributesColl As IHTMLElementCollection
    Dim itemsAttributesEle As IHTMLElement

    Dim pn As Integer

    On Error GoTo 0
    j = 1
    Set ie = CreateObject("InternetExplorer.Application")

    For pn = 1 To 1

        ie.Visible = True
        ie.Navigate "http://www.asos.com/Men/Sale/Accessories/Cat/pgecategory.aspx?cid=2097&CTARef=shop|sale|cat|accessories#parentID=-1&pge=0&pgeSize=36&sort=-1"

        Do
          DoEvents
        Loop Until Not ie.Busy And ie.readyState = READYSTATE_COMPLETE

        Set Doc = ie.Document

        Set itemsColl = Doc.getElementById("items").Children

        For Each itemsEle In itemsColl
            On Error GoTo Skip
            Set itemsAttributesColl = itemsEle.Children

            For Each itemsAttributesEle In itemsAttributesColl


                If itemsAttributesEle.className = "desc" Then
                  Range("B" & j).Value = itemsAttributesEle.innerText 'Description
                  Range("E" & j).Value = itemsAttributesEle.getAttribute("href") 'Link
                End If

                If itemsAttributesEle.className = "productprice" Then
                  Range("C" & j).Value = itemsAttributesEle.Children(0).innerText 'recRP
                  Range("D" & j).Value = itemsAttributesEle.Children(1).innerText 'Outlet current price
                End If
            Next itemsAttributesEle

          j = j + 1
Skip:
        Next itemsEle
        On Error GoTo 0

    Next pn

    ie.Quit
    Set itemsColl = Nothing
    Set itemsEle = Nothing
    Set itemsAttributesColl = Nothing
    Set itemsAttributesEle = Nothing
    Set Doc = Nothing
    Set ie = Nothing

End Sub

Scraping from VBA (very close to working!!)

1 Answers1