Excel VBA - 从网站获取数据

I am usng followng approach to download tabular data from a site. But it's taking approx 1.5 seconds for each loop iteration. I need to speed it up. Any suggestion(s) please?

我使用以下方法从网站下载表格数据。但是每次循环迭代需要大约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

1 个解决方案

#1

You should consider opening the MSXML2.XMLHTTP object only once and then calling Open and Send within each loop iteration. You can store it in a variable before you start the loop. I am sure that most of the time is being spent creating the object and not in the actual Open call. Based on the way you are calling it in your code above, you create a new object in each loop iteration.

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