CODEWITHSUNDEEP
vbaexcelweb-scrapingextract
robsik
robsik

Reputation: 19

Extracting data from website to excel error

I am having a difficulty setting up data extraction from website to Excel. I want to extract exact price of a product to excel. So far I have this code:

Sub GetData()

    Dim objIE As InternetExplorer  'Microsoft Internet Controls library added
    Dim itemEle As Object
    Dim data As String
    Dim y As Integer

    Set objIE = New InternetExplorer
    objIE.Visible = True

    objIE.navigate "https://www.nay.sk/samsung-ue55nu7172"
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    y = 1

    For Each itemEle In objIE.document.getElementsByClassName("price")
    data = itemEle.getElementsByClassName("price")(0).innerText
        y = y + 1
    Next
    data = Range("A1").Value
End Sub

What would you suggest?

Upvotes: 1

Views: 100

Answers (2)

QHarr
QHarr

Reputation: 84465

Do you want every price?

You can list the first two for example this way:

Option Explicit
Public Sub GetInfo()
    Dim sResponse As String, i As Long, html As New HTMLDocument
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.nay.sk/samsung-ue55nu7172", False
        .send
        sResponse = StrConv(.responseBody, vbUnicode)
    End With
    sResponse = Mid$(sResponse, InStr(1, sResponse, "<!DOCTYPE "))
    Dim titles As Object, prices As Object
    With html
        .body.innerHTML = sResponse
        Set titles = .querySelectorAll(".title")
        Set prices = .querySelectorAll(".price")
    End With
    For i = 0 To 1
        Debug.Print titles(i).innerText & prices(i).innerText
    Next i
End Sub

That loop returns you these:

data

You actually have all the elements on the page with a price class stored in the object prices.

You can view all the prices by looping the length of that object/nodeList with: 

For i = 0 To prices.Length - 1
    Debug.Print Prices.item(i).innerText
Next i

Likewise you can loop the .Length of titles but note that it is a different length from prices. There are more prices on the page (or rather elements with a price class versus elements with a title class.

References (VBE>Tools>References):

  1. HTML Object Library

Upvotes: 2

Vityata
Vityata

Reputation: 43595

Try this:

Sub GetData()

    Dim objIE As New InternetExplorer   'Microsoft Internet Controls library added
    Dim itemEle As Object
    Dim data As String
    Dim y As Integer

    objIE.Visible = True

    objIE.navigate "https://www.nay.sk/samsung-ue55nu7172"
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    y = 1

    For Each itemEle In objIE.document.getElementsByClassName("price")
        Cells(y, 1) = itemEle.outertext
        y = y + 1
    Next

End Sub

This is what you get:

enter image description here

To get the correct properties of the itemEle:

  • put a stop sign on the line from the printscreen below
  • select itemEle with your mouse
  • press Shift+F9

enter image description here

Upvotes: 1

Related Questions