Excel vba Problem in set "table variable" using msxmlhttp60

62 views Asked by At

i am beginner in vba , When I run the code I get that the "tb" variable is nothing . Who can help me to fix this? if my code is incorrect how can i get the table data via msxml. i tested in IE mode and it worked fine. but it was slow

thanks a lot

Dim dataArr() As Variant
Dim req As New MSXML2.XMLHTTP60
Dim URL As String
Dim HtmlDoc As New MSHTML.HTMLDocument
Dim tb As MSHTML.IHTMLElement
Dim tr As MSHTML.IHTMLElement
Dim td As MSHTML.IHTMLElement
Dim i As Long, j As Long

URL = "https://rahavard365.com/stock?last_trade=any"
req.Open "GET", URL, False
req.send

If req.Status <> 200 Then
    MsgBox req.Status & "-" & req.statusText
    Exit Sub
End If

HtmlDoc.body.innerHTML = req.responseText

Set tb = HtmlDoc.getElementById("DataTables_Table_0")


ReDim dataArr(1 To tb.Rows.Length, 1 To tb.Rows(1).Cells.Length)

i = 1: j = 1
For Each tr In tb.Rows
    For Each td In tr.Cells
        dataArr(i, j) = td.innerText
        j = j + 1
    Next td
    i = i + 1
    j = 1
Next tr

Sheet1.Range("A1").Resize(UBound(dataArr, 1), _
        UBound(dataArr, 2)).Value = dataArr

End Sub

0

There are 0 answers