CODEWITHSUNDEEP
htmlvbaexcel
kolcinx
kolcinx

Reputation: 2233

Copy HTML table at once, without looping through cells

I want to copy the content of a HTML table. Is it possible to do this without looping through cells in the table? (example of what I'm trying to avoid : Under the comment 'Loop Through Each Table and Download it to Excel in Proper Format.
My table isn't very big, so looping is an option, just wandering if there is a better option.

What I have now and suggested method I would prefer:

'check all opened windows and find the one with PV output
For Each shlWindow In Shell.Windows
    If TypeName(shlWindow.document) = "HTMLDocument" And shlWindow.LocationName = "PV power estimate information" Then
        Set htmlPopupTables = shlWindow.document.getElementsBytagname("table")
        For Each htmlTabl In htmlPopupTables
            If htmlTabl.classname = "data_table" Then
                ' Question :
                ' htmlTabl.Copy 'or something similar
                ' then paste into my excel sheet
            End If
        Next htmlTabl
    End If
Next

I don't have much experience with HTML, so don't hesitate to state even the obvious. :) Thank you.

Upvotes: 2

Views: 8504

Answers (1)

gembird
gembird

Reputation: 14053

There is no such method like htmlTable.Copy but you could use clipboard and paste the html content of the table to excel sheet.

Note: it is necessary to reference internet controls, html object lib. and ms forms. Data will be pasted to active cell on active sheet.

Option Explicit

' Add reference to Microsoft Internet Controls
' Add reference to Microsoft HTML Object Library
' Add reference to Microsoft Forms 2.0 Object Library

Private Const url As String = "file:///c:/temp/table.html"

Sub test()
    Dim ie As SHDocVw.InternetExplorer
    Dim doc As MSHTML.HTMLDocument
    Dim tables As MSHTML.IHTMLElementCollection
    Dim table As MSHTML.HTMLTable
    Dim clipboard As MSForms.DataObject

    Set ie = New SHDocVw.InternetExplorer

    With ie
        .Visible = True
        .navigate url

        While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Wend

        Set doc = .document
        Set tables = doc.getElementsByTagName("table")
        Set table = tables(0)
        Set clipboard = New MSForms.DataObject

        clipboard.SetText table.outerHTML
        clipboard.PutInClipboard
        ActiveSheet.Paste

        .Quit
    End With
End Sub

Demo HTML

<html>
    <head></head>
    <body>
        <table>
            <tr>
                <td>Cell 1</td>
                <td>Cell 2</td>
                <td>Cell 3</td>
            </tr>
            <tr>
                <td>Cell 4</td>
                <td>Cell 5</td>
                <td>Cell 6</td>
            </tr>
            <tr>
                <td>Cell 7</td>
                <td>Cell 8</td>
                <td>Cell 9</td>
            </tr>
        </table>
    </body>
</html>

Result

enter image description here

Upvotes: 5

Related Questions