在vba excel中的刮擦网台的性能

Question

此代码太慢。我需要将数据从HTML表转移到Excel工作表。我无法使用查询表，因为按下按钮后，该表是通过Post方法生成的。 该表是16列x 200+行，

我该怎么做才能使其更快地工作？

'GET THE TABLE
Dim HTMLTables As MSHTML.IHTMLElementCollection
Dim HTMLTable As MSHTML.IHTMLElement
Dim HTMLDiv As MSHTML.IHTMLElement
Dim TableSection As MSHTML.IHTMLElement
Dim TableRow As MSHTML.IHTMLElement
Dim TableCell As MSHTML.IHTMLElement
Dim RowText As String
Dim row As Integer
Dim column As Integer

Dim ws As Worksheet
Set ws = Workbooks("Zautomatyzowany raport produkcyjny.xlsm").Worksheets("PressQualityHold")

row = 1
column = 1
'WAIT for IE
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy
Loop


Set HTMLTables = HTMLDoc.getElementsByTagName("table")

Workbooks("Zautomatyzowany raport produkcyjny.xlsm").Worksheets("PressQualityHold").Cells.Clear

For Each HTMLTable In HTMLTables
    'Debug.Print HTMLTable.ID, HTMLTable.className
    
    If HTMLTable.ID = "table_details" Then
        For Each TableSection In HTMLTable.Children
            'Debug.Print , TableSection.tagName
            
                If TableSection.tagName = "TBODY" Then
                    For Each TableRow In TableSection.Children
                        'RowText = ""
                        For Each TableCell In TableRow.Children
                            ws.Cells(row, column).Value = TableCell.innerText
                            'RowText = RowText & vbTab & TableCell.innerText
                            column = column + 1
                        Next TableCell
                        'Debug.Print , , RowText
                        row = row + 1
                        column = 1
                    Next TableRow
                End If
                
        Next TableSection
    End If
    
Next HTMLTable

IE.Quit

Answer 1

我很抱歉向论坛垃圾邮件，但是我找到了解决方案。
我不确定这是如何以及为什么起作用的，但它确实可以做我想要的。

'GET THE TABLE
Dim HTMLTables As MSHTML.IHTMLElementCollection
Dim HTMLTable As MSHTML.IHTMLElement
Dim HTMLDiv As MSHTML.IHTMLElement
Dim TableSection As MSHTML.HTMLTable
Dim TableRow As MSHTML.IHTMLElement
Dim TableCell As MSHTML.IHTMLElement
Dim RowText As String
Dim row As Integer
Dim column As Integer

Dim ws As Worksheet
Set ws = Workbooks("Zautomatyzowany raport produkcyjny.xlsm").Worksheets("PressQualityHold")

row = 1
column = 1
'WAIT for IE
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy
Loop


Set HTMLTables = HTMLDoc.getElementsByTagName("table")

Workbooks("Zautomatyzowany raport produkcyjny.xlsm").Worksheets("PressQualityHold").Cells.Clear

For Each HTMLTable In HTMLTables
    'Debug.Print HTMLTable.ID, HTMLTable.className
    
    If HTMLTable.ID = "table_details" Then
        For Each TableSection In HTMLTable.Children
            'Debug.Print , TableSection.tagName
            
                If TableSection.tagName = "TBODY" Then
                    
                    Set TableSection = HTMLTables(2)
                    Dim clipboard As MSForms.DataObject
                    Set clipboard = New MSForms.DataObject
                    clipboard.SetText TableSection.outerHTML
                    clipboard.PutInClipboard
                    ws.Range("A1").PasteSpecial
        
                End If
                
        Next TableSection
    End If
    
Next HTMLTable

IE.Quit