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;"> </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&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
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".getElementsByTagName etc.0For-Each.getelementClassName is wrongdoc.getElementsByClassName("table")(0).getElementsByTagName("td")(0).innerText is correctdoc.getElementsByClassName("Something").Item(0) or like that doc.getElementsByClassName("Something")(0)