excelvbainternet-explorerie-automation
Using VBA to click a javascript link, get "object required" error
I am attempting to access a website on IE using VBA to pull a report of the previous weeks transactions. I was able to login and navigate to the report page. However, when I try to click a link for an advanced search I get the error "Object Required"
Below is the HTML I have isolated as belonging to the link:
<!-- Form Actions -->
<input type="button" id="searchtTxn" value="Search"
class="align-right margin-top"
style="float: right; font-size: 11px; margin-top: 5px;">
**<div id="secondary-button"
style="border: none; background: none; height: 26px; font-size: 11px; float: right;">
<a href="javascript:void(0);" id="moreOptions" class="mouseover"><strong>More
Options</strong>**
</a>
</div>
Specifically, I am trying to select the "moreOptions" item. I have also tried selecting based on the "mouseover" class with no luck. I also tried to create a saved report that I could just click based on the class and ID, the HTML for this search is below:
<div class="div-scroll portletContentJS"
style="height:145px !important;" id="savedSearchPortlet"
style="overflow-y: auto;">
<div class="savedsearch-record">
<table style="width:100%;" class='draggable' >
<tr>
<td class="wrap-savedsearch-report">
<div class="constrained">
<a href="javascript:void(0);" class="searchtResultTxn hasTooltip"
id="164035" style="text-decoration: underline;font-size:11px; padding-left: 2px;">Prev Week ACH </a>
<div class="hidden">
<!-- This class should hide the element, change it if needed -->
<table>
<tr>
<td style='word-wrap:break-word;word-break:break-all;max-width:150px;min-width:50px;'>Prev Week ACH</td>
</tr>
</table>
</div>
I am using the below VBA to access the website and navigate to the page I need. The code errors out when I try to pull the Element "moreOptions". I built in a 20 second wait time on the page that doesn't work in case the link wasn't available yet to no avail. I have gone as high as 1 minute with no results.
Sub login1()
Dim IE As Object
Dim HTMLDoc As Object
Dim objCollection As Object
Dim allHREFs As New Collection
Const navOpenInNewTab = &H800
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate "https://www.treasury.pncbank.com/idp/esec/login.ht"
Do While IE.Busy Or IE.readyState <> 4: Loop
Set HTMLDoc = IE.Document
With HTMLDoc
HTMLDoc.getElementById("txtUserID").value = "XXXX"
HTMLDoc.getElementById("txtOperID").value = "XXXXXX"
HTMLDoc.getElementById("txtPwd").value = "XXXXXX"
End With
Set objCollection = IE.Document.getElementById("loginFormButton")
objCollection.Click
Do While IE.Busy Or IE.readyState <> 4: Loop
Application.Wait (Now + TimeValue("0:00:10"))
Set objCollection = IE.Document.getElementById("IR")
objCollection.Click
Application.Wait (Now + TimeValue("0:01:00"))
Set objCollection = IE.Document.getElementById("moreOptions")
objCollection.Click
Do While IE.Busy Or IE.readyState <> 4: Loop
End Sub
Any help here would be greatly appreciated. Please let me know if you need additional details. As it is a banking website I will not be able to provide login credentials but let me know if you need more of the HTML code.
EDIT to Add Full Page HTML
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>PINACLE - PNC</title>
<meta http-equiv="X-UA-Compatible" content="IE=EDGE;"/>
<style>
@import "/portal/shared/style/new-navigation/stylesheet.css";
@import "/portal/shared/style/new-navigation/navbar.css";
</style>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script type="text/javascript" src="/portal/shared/js/jQuery/jQuery.min.js"></script>
<script type="text/javascript" src="/portal/shared/js/jQuery/jquery-migrate.min.js"></script>
<!-- TeaLeaf config file needs to come before SDK -->
<script type="text/javascript" src="/portal/service/js/TealeafSDKConfig.js"></script>
<script type="text/javascript" src="/portal/service/js/TealeafSDK.js"></script>
<script type="text/javascript" src="/portal/shared/js/dojotoolkit/dojo/dojo.js"
djConfig="parseOnLoad:true"></script>
<script type="text/javascript" src="/portal/shared/js/dojotoolkit/dojo/portal.js"></script>
<script type="text/javascript" src="/portal/shared/js/common/navigation.js"></script>
<!-- Carousel - Navigation -->
<script type="text/javascript" src="/portal/shared/js/newPrimaryNav/navbar.js"></script>
<script type="text/javascript" src="/portal/shared/js/newPrimaryNav/jquery.tinycarousel2.js"></script>
<!-- End Carousel - Navigation -->
<!-- Draggable -->
<script type="text/javascript" src="/portal/shared/js/newPrimaryNav/jquery-ui.min.js"></script>
<script type="text/javascript" src="/portal/shared/js/newPrimaryNav/draggable.js"></script>
<link rel="stylesheet" href="/portal/shared/style/jquery-ui.css">
<!-- End Draggable -->
<script type='text/javascript' src='/portal/shared/js/engine.js'></script>
<script type='text/javascript' src='/portal/shared/js/util.js'></script>
<script type='text/javascript' src='/portal/dwr/interface/PortletDisplayHelper.js'></script>
<script type='text/javascript' src='/portal/dwr/interface/AjaxSessionManager.js'></script>
<SCRIPT LANGUAGE="JScript" TYPE="text/javascript">
</SCRIPT>
<script type="text/javascript">
(function() {
var host = '/tmmps/payee.js';
var sn = document.createElement('script');
sn.setAttribute('async', true);
sn.setAttribute('type', 'text/javascript');
sn.setAttribute('src', host);
var s = document.getElementsByTagName('head')[0];
s.appendChild(sn);
})();
</script>
<!-- Add meta tag to enable PINACLE Smart App Banner on mobile devices -->
<script type="text/javascript">
if ( /(iPad).*AppleWebKit.*Mobile.*Safari/.test(navigator.userAgent) ) {
var headNode = document.getElementsByTagName("head")[0];
var sbNode = document.createElement('meta');
sbNode.name = 'apple-itunes-app';
sbNode.content = 'app-id=804888748';
headNode.appendChild(sbNode);
} else if ( /(iPhone|iPod).*AppleWebKit.*Mobile.*Safari/.test(navigator.userAgent) ) {
var headNode = document.getElementsByTagName("head")[0];
var sbNode = document.createElement('meta');
sbNode.name = 'apple-itunes-app';
sbNode.content = 'app-id=874929964';
headNode.appendChild(sbNode);
}
</script>
<!-- Set the required variables for Web Analytics -->
<script type="text/javascript">
var page_data = {
"site_name" : "pin",
"language" : "en",
"brandname" : "PINACLE",
"page_name" : "dashboard",
"page_type" : "",
"user_type" : "",
"page_error" : [],
"events" : []
};
<!-- Set the required theme values for EN Alerts -->
var delay = setSecond(5);
var mcSrc = '/portal/isc/ITS?svcnum=410<e=999&relayState=newMessage&sky=xGTwyoiPngrh1eNFu3twqg%3d%3d';
</script>
<script id="pendo-snippet">
var pendoFlag = 'Y';
var pendoKey = '67a13df9-2e80-4942-4c32-6c799c2b8a67';
var pendoUrl = 'https://cdn.pendo.io/agent/static/';
var account = '10bf187d:013aea5bee72:50e1:01161136';
var visitor = '08757603:016a2273446c:442c:7922f8fb';
if(pendoFlag != null && pendoFlag == 'Y' ){
(function(apiKey){
(function(p,e,n,d,o){var v,w,x,y,z;o=p[d]=p[d]||{};o._q=[];
v=['initialize','identify','updateOptions','pageLoad'];for(w=0,x=v.length;w<x;++w)(function(m){
o[m]=o[m]||function(){o._q[m===v[0]?'unshift':'push']([m].concat([].slice.call(arguments,0)));};})(v[w]);
y=e.createElement(n);y.async=!0;y.src=pendoUrl+apiKey+'/pendo.js';
z=e.getElementsByTagName(n)[0];z.parentNode.insertBefore(y,z);})(window,document,'script','pendo');
// Call this whenever information about your visitors becomes available
// Please use Strings, Numbers, or Bools for value types.
pendo.initialize({
visitor: {
id: visitor
},
account: {
id: account
}
});
})(pendoKey);
}
</script>
<!-- DTM tag for Web Analytics -->
<script type="text/javascript" src="//assets.adobedtm.com/1d90950c926aacaf003e1e8e48aeb1189d4d7901/satelliteLib-da0748631f5bf7f81de415cc298c402328aca822.js"></script>
</head>
<body class="tundra" style="margin: 0px; overflow:hidden;">
<form name="frmLogOut" id="frmLogOut" method="post" action="/idp/esec/logout.ht">
<input type="hidden" name="CST" id="CST" value="yQiJd0LUsfLawQPn9hibYKYebvjqQN2ek5F3WIO-Q6s"/>
</form>
<form name="pinacleMenuForm" method="post" action="/portal/isc/ITS" target="contentIframe">
<input type="hidden" name="svcnum" value=""/>
<input type="hidden" name="lte" value=""/>
<input type="hidden" name="relayState" value=""/>
<input type="hidden" name="sky" value=""/>
<input type="hidden" id="isLogoutProcessStart" name="isLogoutProcessStart" value="false"/>
</form>
<div id="portal-area">
<div id="newNavContainer">
<a id="logo" class="logostyle" target="_top">
<img src='/portal/shared/images/logo_PINACLE.png?05222010' title='PINACLE Home' />
</a>
<div class="utilitybar">
<input type="hidden" id="isLogoutProcessStart" value="false"/>
<!-- Start of Utility bar -->
<div id="utilityMenu" class="noarrow"><a id='LOUT'
href='/portal/esec/logout.ht~popup=N'">
Log Out</a>
</div>
<!-- Quick Links menu -->
<!-- End of Quick Links menu -->
<!-- Begin MessageCenter menu -->
<!-- End of MessageCenter menu -->
<!-- Begin Contact Us menu -->
<!-- End of Contact Us menu -->
<div id="utilityMenu" class="noarrow"><a id='HELP'
href='/portal/isc/ITS?svcnum=615&lte=999&relayState=Admin&sky=n9wMfSLEaF8Tq%2Bq7BKeC%2BKOSkpw%3D~popup=N'">
Help & Training</a>
</div>
<!-- Quick Links menu -->
<!-- End of Quick Links menu -->
<!-- Begin MessageCenter menu -->
<!-- End of MessageCenter menu -->
<!-- Begin Contact Us menu -->
<!-- End of Contact Us menu -->
<!-- Quick Links menu -->
<div class="arrow"><span class="top-levelQL">Quick Links</span>
<div class="dropdown">
<div id="utilityMenuQL">
<a id="HOME" href="https://www6.rbc.com/nj00-wcm/~popup=Y" target="_top">
Canada Express</a>
</div>
</div>
</div>
<!-- End of Quick Links menu -->
<!-- Begin MessageCenter menu -->
<!-- End of MessageCenter menu -->
<!-- Begin Contact Us menu -->
<!-- End of Contact Us menu -->
<!-- Quick Links menu -->
<!-- End of Quick Links menu -->
<!-- Begin MessageCenter menu -->
<!-- End of MessageCenter menu -->
<!-- Begin Contact Us menu -->
<div id="cntsMenu" class="arrow" >
<span class="top-levelCNTS">Contact Us</span>
<div class="dropdown">
<div class="contact-separator" id="utilityMenuCNTS"><a id='PNE' href='/portal/isc/ITS?svcnum=110&lte=32&relayState=Normal Login&sky=hFBb%2BFaxIE2mbqyXUPb4QI8ujhw%3D~popup=N'
target="_top">Phone & Email</a>
</div>
</div>
</div>
<!-- End of Contact Us menu -->
<!-- Quick Links menu -->
<!-- End of Quick Links menu -->
<!-- Begin MessageCenter menu -->
<div id="mcMenu" class="arrow">
<span class="top-levelMC">Message Center</span>
<div class="dropdown">
<div id="utilityMenuMC"><a id='MC' href='/portal/isc/ITS?svcnum=410&lte=999&relayState=managerUser&sky=zUUFoarcvOhT%2BHZsvYhGUxdTrMY%3D~popup=N'
target="_top">View Messages</a>
</div>
<div id="utilityMenuMC"><a id='EVXCN' href='/portal/isc/ITS?svcnum=411&lte=999&relayState=normalLogin&sky=WYvYvPEHQiONA%2FJVJ6Nv2ixnfgc%3D~popup=N'
target="_top">Create Notifications</a>
</div>
<div id="utilityMenuMC"><a id='EVXMN' href='/portal/isc/ITS?svcnum=411&lte=999&relayState=evxMngNotifications&sky=14NR7J0sK%2F%2BOBBwvjZ2KL4sccxE%3D~popup=N'
target="_top">Manage Notifications</a>
</div>
</div>
</div>
<!-- End of MessageCenter menu -->
<!-- Begin Contact Us menu -->
<!-- End of Contact Us menu -->
<div id="utilityMenu" class="noarrow"><a id='PROF'
href='/portal/isc/ITS?svcnum=120&lte=779&relayState=Admin&sky=XLwwWwsdoebibPHq17ltTwMaQRY%3D~popup=N'">
My Profile</a>
</div>
<!-- Quick Links menu -->
<!-- End of Quick Links menu -->
<!-- Begin MessageCenter menu -->
<!-- End of MessageCenter menu -->
<!-- Begin Contact Us menu -->
<!-- End of Contact Us menu -->
<input type="hidden" id="homemenuurl" value="/portal/shared/js/dashboard/dashboard.html" />
<div id="utilityMenu" class="noarrow"><a id='HOME'
href='/portal/shared/js/dashboard/dashboard.html~popup=N'">
Home</a>
</div>
<!-- Quick Links menu -->
<!-- End of Quick Links menu -->
<!-- Begin MessageCenter menu -->
<!-- End of MessageCenter menu -->
<!-- Begin Contact Us menu -->
<!-- End of Contact Us menu -->
</div>
<div class="clear"></div>
<div id="mcSecurityCenter" class="securitycenter">
<a href='/portal/isc/ITS?svcnum=-201&tgt=L1BOU1dlYi9zaG93L2NvbnRlbnQvdHlwZS9TZWN1cml0eQ==~popup=N'
target="_top">
<img src='/portal/shared/images/shield.gif'
alt="Security Center" title="Security Center"/></a>
</div>
</div>
<div id="navbar">
<input type="hidden" id="keepaliveuri" value='/portal/modulecontainer/keepmealive.ht'/>
<a class="buttons prev" href="#"></a>
<div id="tabs" class="viewport">
<ul class="overview ui-sortable" id="sortable">
<li class="border-right"><a class="cursor" id='IR' menuId = '11084'
href="/portal/isc/ITS?svcnum=277&lte=999&relayState=normalLogin&sky=M2ePfNeGG85McrNpd8XuyGxkYTg%3D~popup=N" text="Information Repting" onClick="pendoFunction('')" >
<span class="center-align">
Information <br> Reporting
</span>
</a>
<div id="keepaliveind" data="N"></div>
</li>
<li class="border-right"><a class="cursor" id='SRS' menuId = '35'
href="/portal/isc/ITS?svcnum=966&lte=999&relayState=normalLogin&sky=U9HtsOKdAoQPfjSFA2vaeecoieQ%3D~popup=N" text="Spl Rpts Svc" onClick="pendoFunction('')" >
<span class="center-align">
Special <br> Reports
</span>
</a>
<div id="keepaliveind" data="N"></div>
</li>
</ul>
</div>
<a class="buttons next" href="#"></a>
</div>
<div class="navbar-divider"></div>
<div id="blankDivIR" style="height:0px;width:100%">
<iframe id='blankiframeIR' style='width:100%;height:0px'
scrolling="NO" noresize marginwidth="0" marginheight="0" frameborder="0"
src='/ir/irOpeningPage/initialize.htm'>
</iframe>
</div>
<div id="blankDiv" style="height:0px;width:100%">
<iframe id='blankiframe' style='width:100%;height:0px'
scrolling="NO" noresize marginwidth="0" marginheight="0" frameborder="0"
src='/portal/isc/blank.jsp'>
</iframe>
</div>
<div id="pingRequestsDiv" style="height:0px;width:100%">
<iframe id='pingRequestsiframe' style='width:100%;height:0px'
scrolling="NO" noresize marginwidth="0" marginheight="0" frameborder="0"
src='/idp/pingRequests.ht'>
</iframe>
</div>
<div id="alertDiv" style="height:0px;width:100%">
<iframe id='alertiframe' style='width:100%;height:0px'
scrolling="NO" noresize marginwidth="0" marginheight="0" frameborder="0"
src='/portal/isc/blank.jsp'>
</iframe>
</div>
<div id="contentDiv" style="height:87%;width:100%">
<iframe id='contentIframe' name='contentIframe' style='height:100%;width:100%'
marginwidth="0" marginheight="0" frameborder="0"
src='/portal/shared/js/dashboard/dashboard.html'>
</iframe>
</div>
<div id="dialog-confirm" title="Session About To Expire" style="display: none;">
<p><span class="ui-icon ui-icon-alert" style="float:left; margin:12px 12px 20px 0;border-color: #334455"></span>Your PINACLE session is going to expire. Do you want to extend it ?</p>
</div>
<div id="dialog-confirm-non-pnc" title="Session About To Expire" style="display: none;">
<p><span class="ui-icon ui-icon-alert" style="float:left; margin:12px 12px 20px 0;border-color: #334455"></span>Your session is going to expire. Do you want to extend it ?</p>
</div>
</div>
</div>
<!-- Footer tag for Web Analytics -->
<script type="text/javascript">_satellite.pageBottom();</script>
<script type="text/javascript" >var _cf = _cf || []; _cf.push(['_setFsp', true]); _cf.push(['_setBm', true]); _cf.push(['_setAu', '/resources/54334735b2196aff2ba74ad5d5844c']); </script><script type="text/javascript" src="/resources/54334735b2196aff2ba74ad5d5844c"></script></body>
</html>
Solution
I was able to determine that the "moreOptions" element is inside of the iFrame, I edited my original message to pull in the full HTML for the page. I see that the "IR" element is outside of the iFrame.
To access the elements located inside the tag, we have to find the iframe tag first, then access the elements. You try to use the following code to get elements from the Iframe:
IE.Document.getElementsbyTagName("iframe")(0).contentDocument.getElementbyId("txtcontentinput").Value = "BBB"
IE.Document.getElementsbyTagName("iframe")(0).contentDocument.getElementbyId("btncontentSayHello").Click
[Note] The array index starts from 0. If the website contains multiple iframe tag, make sure using the right array index. Also, you could use the getElementbyId() method to find the iframe tag.
Detail sample code as below:
index page:
<input id="txtinput" type="text" /><br />
<input id="btnSayHello" type="button" value="Say Hello" onclick="document.getElementById('result').innerText = 'Hello ' + document.getElementById('txtinput').value" /><br />
<div id="result"></div><br />
<iframe width="500px" height="300px" src="vbaiframecontent.html">
</iframe>
vbaframeContent.html
<input id="txtcontentinput" type="text" /><br />
<input id="btncontentSayHello" type="button" value="Say Hello" onclick="document.getElementById('content_result').innerText = 'Hello ' + document.getElementById('txtcontentinput').value" /><br />
<div id="content_result"></div>
The VBA script as below:
Sub extractTablesData1()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = True
.navigate ("<your website url>")
While IE.ReadyState <> 4
DoEvents
Wend
'access elements outside the iframe tag and set value.
IE.Document.getElementbyId("txtinput").Value = "AAA"
IE.Document.getElementbyId("btnSayHello").Click
'access elements inside the iframe tag.
IE.Document.getElementsbyTagName("iframe")(0).contentDocument.getElementbyId("txtcontentinput").Value = "BBB"
IE.Document.getElementsbyTagName("iframe")(0).contentDocument.getElementbyId("btncontentSayHello").Click
End With
Set IE = Nothing
End Sub
After running the script, the result as below:
- How to border cells based on where the user click?
- Difference between running a userform in Visual Basic Editor (F5) and opening it with Show?
- VBA delete row working in one for loop but not another
- How to get HEX color value from cell color?
- Remove columns if the number of rows is less than a certain number using vba Excel
- VBA Security : my vbe password is well-set, but still can be hacked by a simple code
- Adjust vertical scrollbar range in Excel userform listbox
- Delete user ribbon from file
- Excel Hide/Show all tabs on Ribbon except custom tab
- Partial value font color in red using vba
- How do I conditionally append a row of Excel data from one sheet to another?
- Efficient formula or code to determine number of errors in range VBA
- Rank numbers including NA()s in Excel 365
- Is it possible to have a named range attached to "activesheet," rather than a specific sheet?
- How to calculate YoY Growth without a Date Field in Pivot?
- Excel hyperlinks 'Can't open specified file' on Mac
- Is it possible to dynamically change a cell/column reference in an excell formula based on another cell
- Excel - create a list from a data column but ignore/remove blank cells
- Split text string in Excel by semicolons and space
- Formula in Excel to count occurrences of substrings within strings
- find closed match value in matrix and return its row and column header in excel
- VBA to allow users to format cells on locked worksheet
- How can my For Each Loop loop to the next row?
- Excel - Return value based in two conditions
- Power Query, M language, Count items in a row with Special condition
- excel group column highlight matching values
- VBA - Save email object (OLEFormat) to file from clipboard
- excel vba projects not closing
- Excel VBA variable Chart Range
- Changing the Date Format for GROUPED dates in a PIVOT TABLE