VBA Web Scraping: Object turns out empty (getelementbyID)

Question

I intend to extract the information from the website below (website is in Malay) containing information on tender awards. https://tender.selangor.my/results?type=tenders

My code is as below, but the 'tenders' object appears as Nothing causing me unable to proceed further. Hope you can share some guidance on what I am doing wrong.

Worksheets("Data").Cells.Clear

Dim xhr As Object
Dim html As New HTMLDocument
Dim tenders As Object, item As Object, item2 As Object
Dim tender As Object
Dim i As Integer

Set xhr = CreateObject("MSXML2.XMLHTTP")

With xhr
    .Open "GET", "https://tender.selangor.my/results?type=tenders", False
    .send

    If .readyState = 4 And .Status = 200 Then
        html.body.innerHTML = .responseText
    Else
        MsgBox "Error" & vbNewLine & "Ready state: " & .readyState & _
        vbNewLine & "HTTP request status: " & .Status
    End If

End With

Set tenders = html.getElementById("DataTables_Table_0")

The DataTables_Table_0 element does not exist on that page. What made you think it existed? — braX
– braX, Commented Feb 4, 2020 at 8:38

MITHU · Accepted Answer · 2020-02-04 13:22:06Z

2

The tabular content that you are interested in are generated dynamically, so you can either make use of Internet Explorer or issue a get http requests with appropriate parameters to parse the json content using third party library. As the first option is easy to go with, I've created an example using the same:

Sub GetInformation()
    Dim tenders As Object

    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .navigate "https://tender.selangor.my/results?type=tenders"
        While .Busy Or .readyState < 4: DoEvents: Wend
        Application.Wait Now + TimeValue("00:00:05")
        Set tenders = .document.getElementById("DataTables_Table_0")
        Debug.Print tenders.innerText
        .Quit
    End With
End Sub

answered Feb 4, 2020 at 13:22

MITHU

1664 gold badges15 silver badges45 bronze badges

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

1 Comment

Azrul Zulkifli Over a year ago

Thank you for the clear explanation with example. My mistake was not realizing the difference between DOM inspector and the actual HTML file. I believe your example emulates the dynamic generation of the content by waiting for it to load (?). Thanks again. I will also look into parsing JSON with 3rd party library as that seems like it would be very useful in the future with IE becoming MS Edge.

Collectives™ on Stack Overflow

VBA Web Scraping: Object turns out empty (getelementbyID)

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