CODEWITHSUNDEEP
htmlvbaweb-scraping
Chris Ellis
Chris Ellis

Reputation: 39

Trying to scrape data off website

I am trying to scrape the address and telephone number from a website. The HTML of the webpage is shown below:

<p class="profileText">
                    <span id="ctl00_PageWrapper_ContentAndSidebar_ContentPlaceHolder1_TextContent_lblExhibitorDetails" class="editorLabel">Checkout.com<br>32 Wigmore Street<br>London<br>W1U 2RP<br>Tel. 0207 323 3888<br></span>
                </p>

Using this code:

Sub Demo()
 Dim URL As String
 Dim ieDoc As Object
 Dim cel As Range

 For Each cel In Range("G2", Range("G" & Rows.Count).End(xlUp))
URL = cel.Value
With CreateObject("InternetExplorer.Application")
    .Visible = False
    .Navigate URL

    Do Until .ReadyState = 4: DoEvents: Loop

    Set ieDoc = .Document
    cel.Offset(, 1).Value = ieDoc.getElementsByClassName("profileText")(0).getElementsByTagName("span")(0).innerText
    .Quit
End With
Next cel
End Sub

But i am having no joy. Can anyone assist me please?

Upvotes: 0

Views: 88

Answers (1)

SIM
SIM

Reputation: 22440

Without the actual urls it is hard to provide you with any solution. However, give it a try:

Sub Demo()
    Dim IE As New InternetExplorer
    Dim ieDoc As HTMLDocument, cel As Range

    For Each cel In Range("G2", Range("G" & Rows.Count).End(xlUp))
        With IE
            .Visible = False
            .navigate cel
            Do Until .readyState = 4: DoEvents: Loop
            Set ieDoc = .document
            r = r + 1: Cells(r, 2) = ieDoc.getElementsByClassName("editorLabel")(0).innerText
        End With
    Next cel
    IE.Quit
End Sub

Reference to add to the library:

1. Microsoft Internet Controls
2. Microsoft Html Object Library

How come you encounter any error when I'm having results. It's a tester:

Sub Data_scraper()
    Dim IE As New InternetExplorer
    Dim ieDoc As HTMLDocument

    With IE
        .Visible = False
        .navigate "http://www.retailbusinesstechnologyexpo.com/exhibitor-profile/citizen-systems-europe"
        Do Until .readyState = 4: DoEvents: Loop
        Set ieDoc = .document
        MsgBox ieDoc.getElementsByClassName("editorLabel")(0).innerText
    End With
    IE.Quit
End Sub

Upvotes: 2

Related Questions