Web Scraping Using VBA Code : How to navigate past the login page?

Question

I'm new to coding and am trying to navigate a website automatically. I can get the code to run and automatically login successfully, however once it reaches the next page, I am unable to interact with any elements. In the example below, I want the macro to click the 'advanced search link' after it logs in. When I run the code, I get a 'Run time error 91: Object variable or With block variable not set.'

The code:

Private Sub CommandButton7_Click()

Dim ie As SHDocVw.InternetExplorer
Dim HTMLDoc As MSHTML.HTMLDocument


Set ie = New InternetExplorerMedium
ie.Visible = True

ie.navigate ("website")

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

Set HTMLDoc = ie.document

HTMLDoc.all.txtUsername.Value = "username"
HTMLDoc.all.txtPassword.Value = "password"

HTMLDoc.all.imgbtnLogin.Click

While ie.Busy Or ie.readyState <> 4: DoEvents: Wend  <<<Code works up to here.

HTMLDoc.getElementById("lnkAdvancedSearch").Click  <<<This yields the error messsage.

End Sub

THE HTML:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<style>
.link {
    font-family: Arial;
    font-size: 8pt;
    font-weight: normal;
    color: blue;
}
.standard {
    font-family: Arial;
    font-size: 8pt;
    font-weight: normal;
    color: black;
}
</style>
</head>

<BODY><FORM onkeypress="javascript:return WebForm_FireDefaultButton(event, 'btnSearch')" id=frmMe method=post name=frmMe action=./todoSummary.aspx oldSubmit="&#10;function submit() {&#10;    [native code]&#10;}&#10;" submit="function WebForm_SaveScrollPositionSubmit() {&#13;&#10;    if (__nonMSDOMBrowser) {&#13;&#10;        theForm.elements['__SCROLLPOSITIONY'].value = window.pageYOffset;&#13;&#10;        theForm.elements['__SCROLLPOSITIONX'].value = window.pageXOffset;&#13;&#10;    }&#13;&#10;    else {&#13;&#10;        theForm.__SCROLLPOSITIONX.value = WebForm_GetScrollX();&#13;&#10;        theForm.__SCROLLPOSITIONY.value = WebForm_GetScrollY();&#13;&#10;    }&#13;&#10;    if ((typeof(this.oldSubmit) != &quot;undefined&quot;) &amp;&amp; (this.oldSubmit != null)) {&#13;&#10;        return this.oldSubmit();&#13;&#10;    }&#13;&#10;    return true;&#13;&#10;}" oldOnSubmit="null" _events="[object Object]">
<DIV class=standard>
<TABLE width="100%">
<TBODY>
<TR>
<TD>
<TABLE>
<TBODY>
<TR>
**<TD style="VERTICAL-ALIGN: top"><A id=lnkAdvancedSearch class=link href="javascript:__doPostBack('lnkAdvancedSearch','')" shape="">Advanced Search:</A></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE></DIV></FORM></BODY>
</html>

^^This is the element I want to interact with^^**

Can you check if HTMLDoc.getElementById("lnkAdvancedSearch") is not Nothing before clicking? You might need to Set HTMLDoc = ie.Document again after the While loop. — Raymond Wu
– Raymond Wu, Commented Oct 20, 2021 at 2:53
Checked and it IS nothing. Adding the ' Set HTMLDoc = ie.Document ' again after the While loop does not fix it however. Still is nothing get Run time error 91. — andrewi
– andrewi, Commented Oct 20, 2021 at 3:10
Without the actual link to test with, it's hard to advise you. You can also try ExecScript as it seems the link is actually calling a javascript so you can execute the javascript directly. see this answer for reference (there's plenty of examples around so you can google it too) @andrewi — Raymond Wu
– Raymond Wu, Commented Oct 20, 2021 at 3:14
Ok I will look it up thanks. Added an image of some of the HTML code prior to the HTML I posted. Noticing tag names 'frames". Wondering if that's the reason I am not retrieving the element. Maybe I need to reference those first? If that's the case not sure how to do so... @RaymondWu — andrewi
– andrewi, Commented Oct 20, 2021 at 16:38
@RaymondWu You were right! Element was hidden in iframe. The answer you referenced was enough to be able to correct my own code. Have it running now. Posted solution below. Thanks for your help! — andrewi
– andrewi, Commented Oct 20, 2021 at 22:05

Jeremy Caney · Accepted Answer · 2021-10-21 00:47:45Z

Ok so found the solution. Element was indeed hidden in an iframe. Therefore, I needed to access the iframe.document first before retrieving the element, just as @RaymondWu suggested in the comments. See below for code that works.

Private Sub CommandButton7_Click()
    
    'Define variables
    
    Dim ie As SHDocVw.InternetExplorer
    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim iframeDoc As MSHTML.HTMLDocument
    Dim HTMLInput As MSHTML.IHTMLElement
    
    'Initialize Internet Explorer and make visible
    
    Set ie = New InternetExplorerMedium
    ie.Visible = True
    
    'Navigate to URL
    
    ie.navigate ("website")
    
    'Wait for browser to load page completely
    
    While ie.Busy Or ie.readyState <> 4: DoEvents: Wend
    
    'Get the HTML document for the page
    
    Set HTMLDoc = ie.document
    
    'Input username, password, and login
    
    HTMLDoc.all.txtUsername.Value = "username"
    HTMLDoc.all.txtPassword.Value = "password"
    
    HTMLDoc.all.imgbtnLogin.Click
    
    'Wait for browser to load page completely
    
    While ie.Busy Or ie.readyState <> 4: DoEvents: Wend
    
    'Get iframe and check if it exists
    
    Set iframeDoc = HTMLDoc.frames("docs").document
    
    If iframeDoc Is Nothing Then
        MsgBox "IFrame was not found."
        ie.Quit
        Exit Sub
        
    End If
        
    'Get element within iframe and check if it exists
    
    Set HTMLInput = iframeDoc.querySelector("a[id =lnkAdvancedSearch]")
        
    If HTMLInput Is Nothing Then
        MsgBox "Element within iframe was not found."
        ie.Quit
        Exit Sub
    Else
       
    'Click element
    
        HTMLInput.Click
    
    End If
       
       
End Sub

Collectives™ on Stack Overflow

Web Scraping Using VBA Code : How to navigate past the login page?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related