excel - 使用 Excel VBA 抓取 HTML 时的 #N/A 值

Question

我在从 .HTM 游戏报告中提取 HTML 代码时遇到问题。正如您所看到的，我使用 Split() 拆分 HTML 文件，它通常会为这个特定的逐个比赛报告创建大约 60,000 行。

现在，对于 100 款游戏中的 99 款来说，这工作得很好，但时不时地(例如使用这个确切的 GameID)它会出于某种原因在仅仅几千行 - 很早进入文档。

我已经检查了相关的 .HTM，它看起来就像我提取的任何其他文档一样。

这是代码片段:

Dim ie As Object, doc As HTMLDocument
Dim Y1 As String, Y2 As String, GameID As String
Dim SourceCode As Worksheet, c As Range

Set ie = New InternetExplorer
Set SourceCode = Worksheets("Source Code")    
Y1 = "2017"
Y2 = "2018"
GameID = "0003"

ie.navigate "http://www.nhl.com/scores/htmlreports/" & Y1 & Y2 & "/PL02" & GameID & ".HTM"

Do
    DoEvents
Loop Until ie.readyState = READYSTATE_COMPLETE

Set doc = ie.document

arr = Split(doc.DocumentElement.innerHTML, vbLf)

SourceCode.Range("A1").Resize(UBound(arr) + 1, 1).Value = Application.Transpose(arr)

这就是发生的情况(偶尔):

有人对如何处理这个错误有建议吗？我完全不知道是什么原因造成的。

Answer 1

Transpose 函数有一个未记录的限制，即 65,536 项。有一些讨论here在 MSDN 上，您可以通过 google 搜索“transpose vba limit”并查看其他一些线程。

因此，您的文件大约有 60,000 行，有些可能超过 65,536 行限制，看起来您的 GameID 已达到该限制。

一种解决方法是创建第二个数组，例如

Dim arr2() As String <-- we will make this a 2d array and fill it from arr

'... later in the code

arr = Split(doc.DocumentElement.innerHTML, vbLf) '<-- get the array with your original code
ReDim Preserve arr2(1 To UBound(arr), 1 To 1) '<-- redim arr2 to be suitable for writing to a range

' now fill the array from arr - basically doing a "manual" transpose
Dim i As Long
For i = 1 To UBound(arr)
    arr2(i, 1) = arr(i)
Next i

完整的工作代码:

Option Explicit

Sub Foo()

    Dim ie As Object, doc As HTMLDocument
    Dim Y1 As String, Y2 As String, GameID As String
    Dim SourceCode As Worksheet, c As Range
    Dim arr As Variant, arr2() As String

    Set ie = New InternetExplorer
    Set SourceCode = Worksheets("Source Code")
    Y1 = "2017"
    Y2 = "2018"
    GameID = "0003"

    ie.navigate "http://www.nhl.com/scores/htmlreports/" & Y1 & Y2 & "/PL02" & GameID & ".HTM"

    Do
        DoEvents
    Loop Until ie.readyState = READYSTATE_COMPLETE

    Set doc = ie.document

    arr = Split(doc.DocumentElement.innerHTML, vbLf)
    ReDim Preserve arr2(1 To UBound(arr), 1 To 1)
    Dim i As Long
    For i = 1 To UBound(arr)
        arr2(i, 1) = arr(i)
    Next i

    SourceCode.Range("A1").Resize(UBound(arr), 1).Value = arr2

    ' see comment by ashleedawg on the other answer
    Set ie = nothing

End Sub