VBA - HttpRequest로 웹페이지 가져오기

VBA코드로 웹페이지를 가져오고, HTML DOM Parsing을 하는 예제 입니다.

Public Sub parsehtml()
    ''Add Tools/Reference first- Microsoft HTML Object Library
    Dim http As Object, html As New HTMLDocument, topics As Object, titleElem As Object, detailsElem As Object, topic As HTMLHtmlElement
    Dim i As Integer
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    http.Open "GET", "https://news.ycombinator.com/", False
    http.Send
    html.body.innerHTML = http.responseText
    Set topics = html.getElementsByClassName("athing")
    i = 2
    For Each topic In topics
        Set titleElem = topic.getElementsByTagName("td")(2)
        Sheets(1).Cells(i, 1).Value = titleElem.getElementsByTagName("a")(0).innerText
        Sheets(1).Cells(i, 2).Value = titleElem.getElementsByTagName("a")(0).href
        Set detailsElem = topic.NextSibling.getElementsByTagName("td")(1)
        Sheets(1).Cells(i, 3).Value = detailsElem.getElementsByTagName("span")(0).innerText
        Sheets(1).Cells(i, 4).Value = detailsElem.getElementsByTagName("a")(0).innerText
        i = i + 1
    Next
End Sub

፠도구/참조에 마이크로소프트 HTML Object Library를 추가해야 합니다.

---

---