2018年2月7日 星期三

Excel VBA querySelectorAll 用法

querySelectorAll 是 DOM 中，用來實作 CSS Selector 的方法，這方法也能在 VBA 中完成，對於選取資料很有幫助，筆者一直都在使用，近期整理一下內容。
以證交所為例，選取網頁中 class = "wp" 的數量。

方法1：
Sub 證交所取wp()
    Const url As String = "http://www.tse.com.tw/zh/"
    Dim IE As Object
    
    Set IE = CreateObject("internetexplorer.application")
    With IE
        .Visible = False
        .navigate url

        Do While .Busy Or .readyState <> 4
            DoEvents
        Loop
        
        Do
            DoEvents
        Loop Until .document.readyState = "complete"
        
        Debug.Print .document.querySelectorAll(".wp").Length
        
        .Quit
    End With
   
    Set IE = Nothing
End Sub

方法2：
Sub 證交所取wp1()
    Const url As String = "http://www.tse.com.tw/zh/"
    Dim IE As Object
    Dim doc As HTMLDocument

    Set doc = New HTMLDocument
    Set IE = CreateObject("internetexplorer.application")
    
    With IE
        .Visible = False
        .navigate url

        Do While .Busy Or .readyState <> 4
            DoEvents
        Loop
        
        Do
            DoEvents
        Loop Until .document.readyState = "complete"
        
        doc.body.innerHTML = .document.body.innerHTML

        .Quit
    End With

    Debug.Print doc.querySelectorAll(".wp").Length

    Set doc = Nothing
    Set IE = Nothing
End Sub
方法2要在設定引用中添加HTML的項目。

程式執行結果：

取內容。
Sub 證交所取wp()
    Const url As String = "http://www.tse.com.tw/zh/"
    Dim IE As Object
    Dim wp As Object
    
    Set IE = CreateObject("internetexplorer.application")
    With IE
        .Visible = False
        .navigate url

        Do While .Busy Or .readyState <> 4
            DoEvents
        Loop
        
        Do
            DoEvents
        Loop Until .document.readyState = "complete"
        
        Set wp = .document.querySelectorAll(".wp")
        For i = 0 To wp.Length - 1
            Debug.Print wp.Item(i).innerhtml
        Next
        
        .Quit
    End With

    Set wp = Nothing
    Set IE = Nothing
End Sub

相關內容：
於