2018年5月4日 星期五

VBA to get web asp table

Reference:
Fetch data from website table using vba


Private Sub finance_data()
    Dim url As String, lastRow As Long
    Dim XMLHTTP As Object, html As Object
    Dim tbl As Object, obj_tbl As Object
    Dim TR As Object, TD As Object
    Dim row As Long, col As Long

    lastRow = Range("A" & Rows.Count).End(xlUp).row

    url = "http://www.taifex.com.tw/chinese/9/9_7_1.asp"

    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
    XMLHTTP.Open "GET", url, False
    XMLHTTP.setRequestHeader "Content-Type", "text/xml"
    XMLHTTP.send

    Set html = CreateObject("htmlfile")
    html.body.innerHTML = XMLHTTP.ResponseText
    Set obj_tbl = html.getelementsbytagname("table")

    row = 1
    col = 1

    For Each tbl In obj_tbl
        If tbl.classname = "table_c" Then
            Set TR = tbl.getelementsbytagname("TR")

            For Each obj_row In TR
                For Each TD In obj_row.getelementsbytagname("TD")
                    Cells(row, col) = TD.innerText
                    col = col + 1
                Next
                col = 1    ' reseting the value
                row = row + 1
            Next
        End If
    Next

沒有留言:

張貼留言