CODEWITHSUNDEEP
vbainternet-explorer
Bimka09
Bimka09

Reputation: 43

How to calculate all tr in tbody (VBA)

Need to extract amount of rows in table. I have tried

Sub Ex()
    Dim ie As Object
    Dim i As Object

    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    ie.Navigate "URL"

    ie.Visible = True

    Set i = ie.document.GetElementsByTagName("tbody")
    i.Rows.Length

But I receive

Method 'Document" of object "IWebBrowser2" failed

I'm new in VBA and don't know about JS. Pleas help

Upvotes: 0

Views: 820

Answers (1)

Zhi Lv
Zhi Lv

Reputation: 21461

Try to use the following code:

Sub Test()
    Dim IE As Object

    Dim startDateText As Object, endDateText As Object

    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "<web site url>"

        While IE.ReadyState <> 4
            DoEvents
        Wend

        'find all of the tr. 
        Set TableRows = IE.document.getElementsByTagName("table")(0).getElementsByTagName("tr")
        Debug.Print TableRows.Length

        'Find tbody rows.
        Set bodyrows = IE.document.getElementsByTagName("table")(0).getElementsByTagName("tbody")(0).getElementsByTagName("tr")

        Debug.Print bodyrows.Length

        'Find tbody rows.
        Set i = IE.document.getElementsByTagName("tbody")(0)
        Debug.Print i.Rows.Length

    End With
    Set IE = Nothing
End Sub

The web page resource as below:

<table>
    <thead> 
        <tr>
            <td>11 MON</td>
            <td>12 TUE</td>
            <td>13 WED</td>
            <td>14 THU</td>
            <td>15 FRI</td>
            <td>16 SAT</td>
            <td>17 SUN</td>
        </tr>
    </thead>
    <tbody> 
        <tr>
            <td id="label2_1" class="class2_1"><input type="text" value="" /></td>
            <td id="label2_2" class="class2_2"><input type="text" value="" /></td>
            <td id="label2_3" name="wedtime2_3"><input type="text" value="" /></td>
            <td id="label2_4"></td>
            <td id="label2_5" class="class2_5"></td>
            <td id="label2_6" name="wedtime"></td>
            <td><input id="txt7" type="text" value="" /></td>
        </tr>
        <tr>
            <td id="label3_1">08:28</td>
            <td id="label3_2" class="class3_2">02:46</td>
            <td id="label3_3" name="wedtime3_3">03:46</td>
            <td id="label3_4">04:46</td>
            <td id="label3_5" class="class3_5">05:46</td>
            <td id="label3_6" name="wedtime">06:46</td>
            <td>07:46</td>
        </tr>
    </tbody>
</table>

If still not working, please try to use the Internet Explorer at a medium integrity level. 

Sub GoToWebsiteTest()
Dim appIE As InternetExplorerMedium
'Set appIE = Nothing
Dim objElement As Object
Dim objCollection As Object

Set appIE = New InternetExplorerMedium
sURL = "http://example.com"
With appIE
    .Navigate sURL
    .Visible = True
End With

Do While appIE.Busy Or appIE.ReadyState <> 4
    DoEvents
Loop

'find the tbody rows.

Set appIE = Nothing
End Sub

or using Set IE = CreateObject("InternetExplorer.ApplicationMedium") to create IE Object.

More information about IE Default Integrity Level, please refer to this article.

Upvotes: 1

Related Questions