为什么用VBA将数据从html表格写入excel表格时速度很慢？

Question

VBA代码运行良好，但是写入数据需要很长时间，有没有其他方法可以从html table/url中获取数据。

Sub pull_option_data()
        Dim tod As String, UnderLay As String, exp As String
        Dim IE As Object
        Dim doc As HTMLDocument

    Dim Tbl As HTMLTable, Cel As HTMLTableCell, Rw As HTMLTableRow, Col As HTMLTableCol
    Dim TrgRw As Long, TrgCol As Long

'START INTERNET EXPLORER AND NAVIGATE TO URL
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    Dim ColOff As Long
    For Nurl = 2 To 151
    UnderLay = ThisWorkbook.Sheets("URLList").Range("A" & Nurl).Value
    ColOff = (Nurl - 2) * 23
TrgRw = 1

    IE.navigate "https://nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?segmentLink=17&instrument=" & UnderLay & ""

        Do While IE.Busy Or IE.readyState <> 4
        Application.Wait DateAdd("s", 1, Now)
        Loop

'GET DATA FROM TABLE AND PUT TO SHEET
    Set doc = IE.document
    Set Tbl = doc.getElementById("octable")
    TrgRw = TrgRw
    For Each Rw In Tbl.Rows
        TrgCol = 1
        For Each Cel In Rw.Cells
            ThisWorkbook.Sheets("Sheet1").Cells(TrgRw, ColOff + TrgCol).Value = Cel.innerText
            TrgCol = TrgCol + Cel.colSpan
        Next Cel
        TrgRw = TrgRw + 1
    Next Rw

TrgRw = TrgRw + 1
Next Nurl

End Sub

Answer 1

想一想任务的更程序化的解决方案：

• 在过程的顶部添加application.screenupdating = False
• 获取.Sheets("URLList")中的所有值作为数组。
• 创建新的二维数组。将键从工作表写入第一列，查找HTML表结果(如您所做的那样)，并将其保存在第二列中。最后，
• 只需粘贴整个数组，而不是遍历工作表