html - VBA：getHTML 作为自定义函数 - 避免多次 HTML 检索

Question

我正在使用它从网页中检索 HTML

Function GetHTML(url As String) As String    
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", url, False
        .Send
        GetHTML = .ResponseText
    End With

End Function

我创建了另一个自定义函数（fromthewebpage）来从 HTML 中提取信息并将其放入 2d 数组中。

Function fromthewebpage(month,user)
    testtext=GetHTML("http://example.com")
    ....
    ....
    fromthewebpage= udarray(month,user)
End function

当我需要工作表中的信息时，我会在单元格中使用 =fromthewebpage(x,y) 来回忆它。

但是数组的大小是 13*4，我需要整个数组，并且每次使用 fromthewebpage(x,y) 函数都独立调用 GetHTML，导致对页面的 52 次检索。有没有办法只调用一次 GetHTML 并为每次使用 fromthewebpage 重用该信息。

我想要么 1）在打开工作簿时调用 GetHTML 并将信息存储在我可以在使用 fromthewebpage 时引用的持久数组中，或者 2）使 fromthewebpage 成为一个数组函数。但我不知道这是否可能以及如何做到这一点。

标签： htmlexcelvbaweb-scrapingcustom-function

Answer 1

因此，我在评论中提到了使用 sub 检索 HTML 信息并将数组返回到工作表的想法。然后，您可以使用函数或公式来索引该信息。

在下面的情况下，子从 worksheet1 范围 A1 中选择 URL。您可以将 sub 绑定到按钮或工作表更改事件，目标为 A1，以便在更改 URL 时更新。

以下是说明性的。我使用 StackOverflow 问题链接来填充我认为是您指定尺寸的数组（如果没有，也很容易更改）。

GetHTML将数组返回到工作表。该函数GetInfo允许您索引到该数组并通过指定数组中的行和列返回一个项目。它将您指定的两个参数镜像到函数签名中以检索字符串。

Option Explicit
Public Sub GetHTML()
    Dim html As htmldocument, ws As Worksheet
    Set html = New htmldocument: Set ws = ThisWorkbook.Worksheets("Sheet1")
    On Error GoTo errhand
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", ws.[A1], False
        .Send
        html.body.innerhtml = .ResponseText
    End With

    Dim arr(0 To 13, 0 To 4)
    Dim i As Long, r As Long, c As Long
    Dim aNodeList As Object
    Set aNodeList = html.querySelectorAll("#question-mini-list h3 > a[href]")
    For i = 0 To (14 * 5) - 1
       If i = 0 Then
           arr(r, c) = aNodeList.Item(i)
       ElseIf i Mod 5 = 0 And i <> 0 Then
           r = r + 1: c = 0
           arr(r, c) = aNodeList.Item(i)
       Else
          c = c + 1
          arr(r, c) = aNodeList.Item(i)
       End If
    Next

    ws.[B1].Resize(UBound(arr, 1) + 1, UBound(arr, 2) + 1) = arr
errhand:
    If Err.Number <> 0 Then Err.Clear
End Sub

Public Function GetInfo(ByVal r As Long, ByVal c As Long) As String
    GetInfo = ThisWorkbook.Worksheets("Sheet1").Range("B1:F14").Cells(r + 1, c + 1)
End Function

---

在工作表 UDF 调用中：

---

Worksheet_Change 事件

如果将子GetHTML执行链接到Worksheet_ChangeA1 中带有 URL 的事件，则在工作表 1 代码窗格中：

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Address = [A1].Address Then
        GetHTML
    End If
    Application.EnableEvents = True
End Sub