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, Oct 20 '21 at 02: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, Oct 20 '21 at 03: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](https://stackoverflow.com/questions/31521205/how-to-find-and-call-javascript-method-from-vba) for reference (there's plenty of examples around so you can google it too) @andrewi — Raymond Wu, Oct 20 '21 at 03: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, Oct 20 '21 at 16:38
The element could be hidden in iframe which in this case, you need to access the iframe.document first then getElementById. Read this [answer](https://stackoverflow.com/questions/44902558/accessing-object-in-iframe-using-vba) for more info — Raymond Wu, Oct 20 '21 at 17:16
@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, Oct 20 '21 at 22:05

score 0 · Answer 1 · edited Oct 21 '21 at 00:47

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

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

1 Answers1