Excel VBA - 从网站获取数据

Question

我正在使用以下方法从网站下载表格数据。但每次循环迭代大约需要 1.5 秒。我需要加快速度。请问有什么建议吗？

Sub GetData_Sai()
    Dim htm As Object
    Dim x As Long, y As Long

    Set htm = CreateObject("htmlFile")
    Application.ScreenUpdating = False

    Row = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    For p = Range("F1") To Range("F1") + 200
        Debug.Print p
        Set htm = CreateObject("htmlFile")
        With CreateObject("msxml2.xmlhttp")
            .Open "GET", <site_url_goes_here>, False
            .send
            htm.body.innerhtml = .responsetext
        End With

        With htm.getelementbyid("item")
            Sheet2.Cells(Row, 4).Value = p
            For x = 1 To .Rows.Length - 1
                For y = 0 To .Rows(x).Cells.Length - 1
                    Sheet2.Cells(Row, y + 1).Value = .Rows(x).Cells(y).innertext
                Next y
                Row = Row + 1
            Next x
        End With
        Set htm = Nothing

        If p Mod 10 = 0 Then ThisWorkbook.Save
    Next

    Range("F1") = p

    Application.ScreenUpdating = True
    MsgBox "Done", vbInformation
End Sub

Answer 1

您应该考虑仅打开 MSXML2.XMLHTTP 对象一次，然后在每次循环迭代中调用 Open 和 Send。您可以在开始循环之前将其存储在变量中。我确信大部分时间都花在创建对象上，而不是实际的 Open 调用上。根据您在上面的代码中调用它的方式，您在每次循环迭代中创建一个新对象。