VBA .innertext using getelements*, cannot locate areas in html code

Question

I am trying to create a macro to get the innertext from an internal webpage. I am not sure on how to properly locate the where the text is and would appreciate some direction and possibly some explanation on the approach.

I have tried numerous variations of using the getelementsby/tagname/classname to no avail. im not sure i understand the logic behind locating the areas after using the inspection function.

Var = ie.document.getelementClassName("sections").getElementsByTagName("table").Item(0).innerText

'also tried
Var = ie.document.getelementClassName("sections").getElementsByTagName("table").Item(1).getElementsByTagName("tr").Item(2).getElementsByTagName("td").Item(0).innerText

Var = ie.document.getelementTagName("section").getElementsByTagName("table").Item(1).getElementsByTagName("tr").Item(2).getElementsByTagName("td").Item(0).innerText


ActiveCell.Offset(0, 1).Value = Var

<html class=" js flexbox canvas canvastext webgl no-touch geolocation postmessage websqldatabase indexeddb hashchange history draganddrop websockets rgba hsla multiplebgs backgroundsize borderimage borderradius boxshadow textshadow opacity cssanimations csscolumns cssgradients cssreflections csstransforms csstransforms3d csstransitions fontface no-generatedcontent video audio localstorage sessionstorage webworkers no-applicationcache svg inlinesvg smil svgclippaths" lang="" style=""><!--<![endif]--><head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <title>NTC Tracking</title>
    <meta name="description" content="">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="apple-touch-icon" href="apple-touch-icon.png">

    <link rel="stylesheet" href="/Content/bootstrap.min.css">
    <!--        <link rel="stylesheet" href="~/Content/bootstrap-theme.min.css">-->
    <!--For Plugins external css-->
    <link rel="stylesheet" href="/Content/plugins.css">



    <!--Theme custom css -->
    <link rel="stylesheet" href="/Content/style.css">

    <!--Theme Responsive css-->
    <link rel="stylesheet" href="/Content/responsive.css">

    <script src="/Scripts/vendor/modernizr-2.8.3-respond-1.4.2.min.js"></script>


</head>
<body data-spy="scroll" data-target="#main-navbar">
    <!--[if lt IE 8]>
        <p class="browserupgrade">You are using an <strong>outdated</strong> browser. Please <a href="http://browsehappy.com/">upgrade your browser</a> to improve your experience.</p>
    <![endif]-->

    <div class="preloader" style="display: none;"><div class="loaded" style="display: none;">&nbsp;</div></div>
    <div id="menubar" class="main-menu">
        <nav class="navbar-default navbar-fixed-top" style="background-color:#ffc038; padding:20px;">
            <div class="container">
                <!-- Brand and toggle get grouped for better mobile display -->
                <div class="navbar-header">
                    <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1" aria-expanded="false">
                        <span class="sr-only">Toggle navigation</span>
                        <span class="icon-bar"></span>
                        <span class="icon-bar"></span>
                        <span class="icon-bar"></span>
                    </button>
                    <a class="" href="http://10.102.18.162/"><img src="/images/msjlogo.png" style="max-width:50%; margin-top:-20px;"></a>

                </div>

                <!-- Collect the nav links, forms, and other content for toggling -->
                <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
                    <ul class="nav navbar-nav navbar-right">
                        <li><a href="#"><span style="font-weight:100; font-size:10px;">Proxy Plus(5/22/2019 8:20:00 AM) | IQ(5/22/2019 8:31:00 AM) | Vendors(5/21/2019 2:24:00 PM) | USPS(5/22/2019 8:43:00 AM) | International(5/21/2019 2:24:00 PM)</span></a></li>



                    </ul>
                </div><!-- /.navbar-collapse -->
                <div>
                    <a class="navbar-brand" href="/"><h3>NTC Tracking</h3></a>
                </div>
                <div style="clear:both; margin-bottom:20px;"></div>
                <div>

            </div>
            </div><!-- /.container-fluid -->
        </nav>
    </div>
    <!--Home page style-->
    <header id="home" class="sections">

    </header>

    <!-- Sections -->




<header id="home">
    <div class="container">
        <h2 align="center">Search Job</h2>
        <div class="col-md-6 col-md-offset-3 col-sm-6 col-xs-12">
            <p align="center">Description of this view, testing space and top bar at the same time</p>
            <p align="center">There are some hiding fields due to the web space, if you want to see them click on export</p>
            <p align="center">Description of this view, testing space and top bar at the same time</p>
            <p align="center">Description of this view, testing space and top bar at the same time</p>
        </div>

    </div>
    <br>
</header>
<section class="sections">
    <div class="portfolio">
        <div align="center" class="portfolio-item">
            <h5 align="center">Job Number </h5><input id="PPNumber" name="PPNumber" type="text" value="P23315"><br>
            <a onclick="submitdata();" href="#" class="btn btn-primary">Search </a>
            <br><br>

                <div>

                    <p></p>
                </div>
                <div style="float:left"><h3 align="left">JOB</h3></div>
                <table class="table" style="font-size:11px;">
                    <tbody><tr>
                        <th>
                            Job #
                        </th>
                        <th width="20%">
                            Job Name
                        </th>
                        <th>
                            MeetingDate
                        </th>
                        <th>
                            DropDate
                        </th>
                        <th>
                            NTCMailDate
                        </th>
                        <th>
                            LI#
                        </th>
                        <th>
                            Total Pieces
                        </th>
                        <th width="5%">
                            Day 40 On
                        </th>
                        <th width="5%">
                            Logistics Processed
                        </th>
                        <th width="5%">
                            IQ Status
                        </th>
                        <th>
                            MustMail Comments
                        </th>
                        <th>
                            Total Batch Completed
                        </th>
                        <th>
                            Actual Status
                        </th>
                        <th>
                            Options
                        </th>

                    </tr>
                        <tr>
                            <td>
<a href="/Report/Batchdetail/P23315-010" target="_blank">P23315-010</a>                            </td>
                            <td width="20%">
                                ATLANTICA YIELD PLC      <----****I NEED THIS****               
                            </td>
                            <td>
                                6/20/2019
                            </td>
                            <td>
                                5/13/2019
                            </td>
                            <td>
                                5/13/2019
                            </td>
                            <td>
                                LI-8154090
                            </td>
                            <td>
                                2200
                            </td>
                            <td width="5%">
                                5/11/2019
                            </td>
                            <td width="5%">
                                4386
                            </td>
                            <td width="5%">
                                Mailed
                            </td>
                            <td>
                                MUST MAIL 5/14
                            </td>
                            <td>
                                11 out of 11
                            </td>
                                <td>
                                    Foreign Client
                                </td>
                                                                                <td>
                                                <a class="btn btn-default" href="/Report/Reopenjob?jobnumber=P23315&amp;jobref=P23315-010">Reopen Job</a>
                                            </td>
                        </tr>
                </tbody></table>
                <br>
                 <br>
         </div>
    </div>
</section>
<script>

function submitdata(){


    var valtext = $("#PPNumber").val();//you can do also by  getelementbyid
    window.location.href = '/Report/Search/' + valtext;

}
function ShowMessage() {
    var result = prompt("Please insert a comment if required.", "");
    if (result == null) {
        return false; //break out of the function early
    }
    document.getElementById('comments').value = result;
    return true;
}

</script>






    <div class="scroll-top">

        <div class="scrollup">
            <i class="fa fa-angle-double-up"></i>
        </div>

    </div>

    <!--Footer-->
    <footer id="footer" class="footer">
        <div class="container">

            <div class="row">


                <div class="socio-copyright">

                    <div class="social">

                    </div>

                    <p>Made by Broadridge 2017. All rights reserved.</p>
                </div>

            </div>
        </div>

    </footer>
    <script src="/Scripts/vendor/bootstrap.min.js"></script>

    <script src="/Scripts/vendor/jquery-1.11.2.min.js"></script>
    <script src="/Scripts/plugins.js"></script>
    <script src="/Scripts/main.js"></script>



</body></html>

error 438

Stavros Jon · Accepted Answer · 2019-05-22 14:53:19Z

Here's the general logic of how to access the cells of an HTML table:

Sub test()
Dim sht As Worksheet
Dim doc As New HTMLDocument
Dim targetTable As HTMLTable
Set sht = ThisWorkbook.Worksheets("Sheet1")
doc.body.innerHTML = sht.Range("M1") 'I just stored the html code in cell M1 as a string for the sake of demonstration

Set targetTable = doc.getElementsByClassName("table")(0) 'Get the first element from a collection of elements whose class name is "table"
Debug.Print targetTable.Rows(0).Cells(0).innerText 'Get the first row from the collection of rows that belong to the table and the first cell from the collection of cells that belong to this row.
End Sub

The code above will print Job # in the immediate window. This is the inner text of the first cell of the first row (which is the header of the first column). You can get the rest of the values accordingly.

Things to remember:

doc.getElementsByClassName("table") is a collection of elements whose class name is "table"
Same applies to .getElementsByTagName etc.
The first item in the collection has an index of 0
You can loop through all the elements in a collection using a For-Each
This .getelementClassName is wrong
This doc.getElementsByClassName("table")(0).getElementsByTagName("td")(0).innerText is correct
You can access an item in the collection either like that doc.getElementsByClassName("Something").Item(0) or like that doc.getElementsByClassName("Something")(0)

QHarr · Accepted Answer · 2019-05-22 14:45:34Z

The error:

The method is

getElementsByClassName

or

getElementsByTagName

These return collections which you then index into e.g.

ie.document.getElementsByClassName("className")(0)  'first element

Making those changes should handle your initial error.

Targeting specific row and column:

You can use nth-of-type if automating with IE i.e. tr:nth-of-type(rowNumberHere) , td:nth-of-type(columnNumberHere).

I think you are after the second row first column so I would use css selectors

ie.document.querySelector(".table tr:nth-of-type(2) td:nth-of-type(1)").innerText

Modern browsers are optimized for css selectors so this should be an efficient method.

Entire table:

An easy way to copy an entire table is to use the clipboard

Option Explicit
Public Sub GetInfo()
    Dim ie As New InternetExplorer, url As String, ws As Worksheet
    Dim t As Date, clipboard As Object, hTable As Object
    url = "url"
    Const MAX_WAIT_SEC As Long = 10

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

    With ie
        .Visible = True
        .Navigate2 url

        While .Busy Or .readyState < 4: DoEvents: Wend

        With .document

            t = Timer
            Do
                On Error Resume Next
                Set hTable = .querySelector(".table")
                On Error GoTo 0
                If Timer - t > MAX_WAIT_SEC Then Exit Do
            Loop While hTable Is Nothing
        End With

        If hTable Is Nothing Then Exit Sub
            clipboard.SetText hTable.outerHTML
            clipboard.PutInClipboard
            ws.Range("A1").PasteSpecial
        .Quit
    End With
End Sub

Looping rows and columns of a table:

If you want to loop rows and columns of a table and write out, see here.

Collectives™ on Stack Overflow

VBA .innertext using getelements*, cannot locate areas in html code

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related