Results 1 to 2 of 2

Thread: Loop through pages & webscrape

  1. #1

    Loop through pages & webscrape

    Hi There,

    I have currently prepared VBA code that web scrapes product item and price on Ebay. The code uses an XMLHttpRequest and an HTML document through Excel VBA and then outputs product item and price to an Excel Spreadsheet.

    I am trying to add a page loop so it runs through each of the pages and then outputs the resulting product title and produce price to an Excel spreadsheet. I will provide the Excel file and require refinement of code.

    I know I need to target pagination, but I am drawing blanks.

    Could anyone help, please
    Sub get_vid_page()Const ebayurl As String = _ ""
    Dim XmlReq As New XMLHTTP60
    Dim HtmlDoc As New MSHTML.HTMLDocument
    Dim prodnames As MSHTML.IHTMLElementCollection
    Dim proditem As MSHTML.IHTMLElement
    Dim prodprices As MSHTML.IHTMLElementCollection
    Dim prodprice As MSHTML.IHTMLElement
    Dim i As Integer
    Dim EbayPages As MSHTML.IHTMLElementCollection
    XmlReq.Open "GET", ebayurl, False
    If XmlReq.Status <> 200 Then
     MsgBox "Problem " & vbNewLine & XmlReq.Status & " - " & XmlReq.statusText
     Exit Sub
    End If
    HtmlDoc.body.innerHTML = XmlReq.responseText
    i = 1
    Set prodnames = HtmlDoc.getElementsByClassName("s-item__title")
    Set prodprices = HtmlDoc.getElementsByClassName("s-item__price")
    'Set EbayPages = HtmlDoc.getElementsByClassName("pagination__item") attempting to target this and loop through all search pages and output item name and price for all 10 pages?
    For Each proditem In prodnames
       Cells(i, 1).Value = proditem.innerText
       i = i + 1
    i = 1
    For Each prodprice In prodprices
      Cells(i, 2).Value = prodprice.innerText
       i = i + 1
    End Sub
    Attached Files Attached Files
    Last edited by Aussiebear; 12-10-2022 at 01:17 PM. Reason: Added the code for others to view

  2. #2
    VBAX Regular
    May 2018
    It seems to only grab the first 2 pages. I'll keep working on a solution for this if nobody else beats me to the solution =)
    Last edited by Grade4.2; 12-10-2022 at 11:14 AM.
    If you only ever do what you can , you'll only ever be what you are.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts