Excel VBA to Submit UserName & Password on IE

BPtexan · Feb 18, 2010

I posted this on an earlier thread, but was urged to start a new one.

I'm trying to create a program that will automatically log in to a website that requires a username & password. I am able to enter the username and password in the correct text boxes, but I can not get the form to submit to approve the login. It refreshes and removes the password.

I have attached my code. The web url is inside it. Thanks!

Code:

Sub IE_login()
    Dim ie As InternetExplorer
    Dim C
    Dim ULogin As Boolean, ieForm
    Dim MyPass As String, MyLogin As String
    Set ie = New InternetExplorer
    ie.Visible = True
    ie.Navigate "[URL]https://applications.dacgroup.com/login.aspx[/URL]"
    'Loop until ie page is fully loaded
    Do Until ie.ReadyState = READYSTATE_COMPLETE
    Loop
    'Look for password Form by finding test "Password"
    For Each ieForm In ie.Document.forms
            ieForm(5).Value = "****"
            ieForm(6).Value = "****"
            'login
            ieForm.submit
            Exit For
    Next
    End Sub

Norie · Feb 18, 2010

Why are you looping through all the forms on the page?

Are you even doing that? The reason I ask is because you just seem to be ending the loop immediately.

Also you don't seem to be checking for anything to identify the form you want.

The id for the form appears to be 'aspnetform' (or something like that), so why not use GetElementByID("aspnetform") to get a reference to the form.

BPtexan · Feb 18, 2010

I changed the code after copying it from http://www.vbaexpress.com/kb/getarticle.php?kb_id=399

I'm a novice vba user. The only vba I use is what I can record and a few things I've learned along the way. I've never controlled IE through vba.

Norie · Feb 18, 2010

Is there any reason why you want/need to use Excel VBA?

John_w · Feb 18, 2010

Try this, based on the code in the other thread.

AFAIK there are 2 ways of submitting a form: calling the form's submit method; or callling the form input button's click method. In this case I think submit (i.e. LoginForm.submit) is insufficient because the input button has an o n c l i c k attribute which calls a Javascript function which does some sort of validation of the page (I haven't looked closely at the Javascript code). Therefore the button's click event must be called instead so that the Javascript function is called. I'm guessing that LoginForm.submit doesn't fire the o n c l i c k event.

Code:

'Needs references to Microsoft HTML Object Library and Microsoft Internet Controls

Option Explicit

Sub Test()

    Const cURL = "https://applications.dacgroup.com/login.aspx"
    Const cUsername = "XXXX"    'REPLACE XXXX WITH YOUR USER NAME
    Const cPassword = "YYYY"    'REPLACE YYYY WITH YOUR PASSWORD
    
    Dim IE As InternetExplorer
    Dim doc As HTMLDocument
    Dim LoginForm As HTMLFormElement
    Dim UserNameInputBox As HTMLInputElement
    Dim PasswordInputBox As HTMLInputElement
    Dim SignInButton As HTMLInputButtonElement
    Dim HTMLelement As IHTMLElement
    
    Set IE = New InternetExplorer
    
    IE.Visible = True
    IE.navigate cURL
    
    'Wait for initial page to load
    
    Do While IE.readyState <> READYSTATE_COMPLETE Or IE.busy: DoEvents: Loop
    
    Set doc = IE.document
    
    'Get the only form on the page
    
    Set LoginForm = doc.forms(0)
    
    'Get the User Name textbox and populate it
    '< input name="ctl00$ct$UserName" type="text" maxlength="30" id="ctl00_ct_UserName" style="width:160px;" />
 
    Set UserNameInputBox = LoginForm.elements("ctl00$ct$UserName")
    UserNameInputBox.Value = cUsername
    
    'Get the password textbox and populate it
    '< input name="ctl00$ct$Password" type="password" maxlength="30" id="ctl00_ct_Password" style="width:160px;" />

    Set PasswordInputBox = LoginForm.elements("ctl00$ct$Password")
    PasswordInputBox.Value = cPassword
    
    'Get the form input button and click it
    '< input type="submit" name="ctl00$ct$uxBtnLogin" value="Sign In" o n c l i c k="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$ct$uxBtnLogin", "", true, "Login", "", false, false))" id="ctl00_ct_uxBtnLogin" />
    
    Set SignInButton = LoginForm.elements("ctl00$ct$uxBtnLogin")
    SignInButton.Click
            
    'Wait for the new page to load
    
    Do While IE.readyState <> READYSTATE_COMPLETE Or IE.busy: DoEvents: Loop
    
    'Get the HTML document of the new page
    
    Set doc = IE.document
    
    'Determine whether login succeeded or not
    
    If InStr(doc.body.innerText, "Invalid Login Information") = 0 Then
        MsgBox "Login succeeded"
    Else
        MsgBox "Login failed"
    End If
        
    Debug.Print "Current URL: " & IE.LocationURL
        
End Sub

BPtexan · Feb 19, 2010

Works great. Thanks!

ocnuybear · Mar 10, 2010

Hi John_w,

Your code is very well written with a good explanation for a beginner like me !.
However I cannot get my login to work. I get a runtime error: "91 Object variable or with block variable not set".
I have stepped throught the code with F8 and the variable at this line:
Set LoginForm = doc.forms(0) the variable LoginForm = Nothing while when trying the original code it gives me LoginForm = "[object]".
Here's my code:

Code:

'Needs references to Microsoft HTML Object Library and Microsoft Internet Controls

Option Explicit

Sub Test()

    Const cURL = "http://mtcolifants/faces/loginProper.jsp"
    'Const cURL = "https://applications.dacgroup.com/login.aspx"
    
    Const cUsername = "XXXX"    'REPLACE XXXX WITH YOUR USER NAME
    Const cPassword = "YYYY"    'REPLACE YYYY WITH YOUR PASSWORD
    
    Dim IE As InternetExplorer
    Dim doc As HTMLDocument
    Dim LoginForm As HTMLFormElement
    Dim UserNameInputBox As HTMLInputElement
    Dim PasswordInputBox As HTMLInputElement
    Dim SignInButton As HTMLInputButtonElement
    Dim HTMLelement As IHTMLElement
    
    Set IE = New InternetExplorer
    
    IE.Visible = True
    IE.Navigate cURL
    
    'Wait for initial page to load
    
    Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    
    Set doc = IE.Document
    
    'Get the only form on the page
    
    Set LoginForm = doc.forms(0)
    
    'Get the User Name textbox and populate it
    '< input name="ctl00$ct$UserName" type="text" maxlength="30" id="ctl00_ct_UserName" style="width:160px;" />
 
    'Set UserNameInputBox = LoginForm.elements("ctl00$ct$UserName")
    'UserNameInputBox.Value = cUsername
    
    Set UserNameInputBox = LoginForm.elements("userName")
    UserNameInputBox.Value = cUsername
 
    
    'Get the password textbox and populate it
    '< input name="ctl00$ct$Password" type="password" maxlength="30" id="ctl00_ct_Password" style="width:160px;" />

    Set PasswordInputBox = LoginForm.elements("ctl00$ct$Password")
    PasswordInputBox.Value = cPassword
    
    'Get the form input button and click it
    '< input type="submit" name="ctl00$ct$uxBtnLogin" value="Sign In" o n c l i c k="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$ct$uxBtnLogin", "", true, "Login", "", false, false))" id="ctl00_ct_uxBtnLogin" />
    
    Set SignInButton = LoginForm.elements("ctl00$ct$uxBtnLogin")
    SignInButton.Click
            
    'Wait for the new page to load
    
    Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    
    'Get the HTML document of the new page
    
    Set doc = IE.Document
    
    'Determine whether login succeeded or not
    
    If InStr(doc.body.innerText, "Invalid Login Information") = 0 Then
        MsgBox "Login succeeded"
    Else
        MsgBox "Login failed"
    End If
        
    Debug.Print "Current URL: " & IE.LocationURL
        
End Sub

and here is my source code from the login page:

Code:

     <link rel="stylesheet" href="common/header.css" type="text/css">
     <link rel="stylesheet" href="common/style.css" type="text/css">
    
     





"http://www.w3.org/TR/html4/loose.dtd">

******** language="javascript" type="text/javascript">
    // Back button/link
    function doBack() {
        history.back(1);
    }      

        // Back button/link
    function back() {
        history.back(1);
    }      

    // Delete item function uses generic form as name of form to submit
    function confirmDelete(action,id) {
        if (confirm("The user will be deleted permanantly, do you want to continue?")) {
            document.form.oid.value=id;
            document.form.action.value=action;
            document.form.submit();
        }
    }

     function confirmItemDelete(action,id) {
        if (confirm("The Item will be deleted permanantly, do you want to continue?")) {
            document.form.itemOid.value=id;
            document.form.action.value=action;
            document.form.submit();
        }
    }

    // confirms kickiing/logging out of user from system
    function confirmUserKick(formName, oidValue)  {
        if (confirm("Are you sure you want to LOGOUT this user? ")) {
            var theForm = document.getElementById(formName);
            var userName = document.getElementById('userName');
            theForm.userName.value = oidValue;
            theForm.submit(); 
            return true;
     } else
            return false;
    }
    
    // generic search popup window
    function search() {
        var searchtext = prompt("Please enter the name of the user to search for");
        if (searchtext!=null && searchtext!="") {
            document.form.searchtext.value=searchtext;
            document.form.action.value='searchtext';
            document.form.submit();
        }                
    }
    
    // page refresh
    function refresh() {
        document.form.searchtext.value="";
        document.form.submit();
    }

   // form submit
    function submitFormNoParams() {
        document.form.submit();
    }
    
   // form submit
    function submitFormWithActionParam(action) {
        document.form.action.value=action;
        document.form.submit();
    }     
    
    // form submit
    function submitForm(bname,task) {
        var bname=document.getElementById(bname);  
        bname.disabled='true';
        var action = document.getElementById('action');  
        action.value = task;  
        document.form.submit();
    }      

    function pageLoading() {
        if (document.getElementById) {  // DOM3 = IE5, NS6
            document.getElementById('hidepage').style.visibility = 'hidden';
        }else {
            if (document.layers) {  // Netscape 4
                document.hidepage.visibility = 'hidden';
            } else {  // IE 4
                document.all.hidepage.style.visibility = 'hidden';
            }
        }
    }
       function PopUpWindow(url,windowname,width,height,scroll,rez,tool,menu,loc,horz,vert)     {
            if(vert=='top')  {
                wint = 0;
            }

            if(vert=='middle') {
                wint = (screen.height - height) / 2;
            }

            if(vert=='bottom') {
                wint = (screen.height - height);
            }

            if(horz=='center') {
                winl = (screen.width - width) / 2;
                popupwindow = 
                ************(url,windowname, 

'width='+width+',height='+height+',screenX='+winl+',screenY='+wint+',left='+winl+',top='+wint+',scrollbars='+scroll+',resizable=

'+rez+',toolbar='+tool+',menubar='+menu+',location='+loc);
                popupwindow.focus();
            }
            else {
                if(horz=='left')  {
                    var winl = 0;
                    popupwindow = 
                    ************(url,windowname, 

'width='+width+',height='+height+',screenX='+winl+',screenY='+wint+',left='+winl+',top='+wint+',scrollbars='+scroll+',resizable=

'+rez+',toolbar='+tool+',menubar='+menu+',location='+loc);
                    popupwindow.focus();
                }

                if(horz=='right') {
                    winl = (screen.width - width);
                    popupwindow = 
                    ************(url,windowname, 

'width='+width+',height='+height+',screenX='+winl+',screenY='+wint+',left='+winl+',top='+wint+',scrollbars='+scroll+',resizable=

'+rez+',toolbar='+tool+',menubar='+menu+',location='+loc);
                    popupwindow.focus();
                }
            }
        }
        
*********>
   
     ******** language="JavaScript1.2">
      function focusUN() {
            document.loginform.userName.focus();
     }
  *********>
 



<!-- Display Error messages-->






<table height="100%" width="100%"><tbody><tr><td align="center" height="100%" valign="center" width="100%">





[IMG]http://www.mrexcel.com/forum/images/ele.jpg[/IMG]
[B]Olifants (b20091013) [/B]



<form name="loginform" method="post" action="loginServlet">
    <input name="action" value="Login" type="hidden">
        
        
            
        
        
            
        
        
            
        
    <table align="center" border="0" cellpadding="0" cellspacing="0" width="20%"><tbody><tr><td height="29" nowrap="nowrap">User Name</td><td><input  name="userName" value="" size="20" type="text"></td></tr><tr><td height="29">Password</td><td><input name="userPassword" value="" size="20" type="password"></td></tr><tr><td height="29">
</td><td align="center">
<input name="action" value="Login" type="submit"><input>

type="reset" value="Cancel" *******="focusUN()"></td></tr></tbody></table>
</form>




******** language="JavaScript1.2">
     document.loginform.userName.focus();
*********>
</td></tr></tbody></table>


  
    
  
<table border="0" cellpadding="0" cellspacing="0" width="100%"><tbody><tr> <td class="admin_title_td">[B]Current online users: List[/B] 
    </td></tr></tbody></table>

<hr noshade="noshade" size="2">
  
    
    
      
      
      
      
      
    
    
       
        
        
        
        
           
     
       
        
        
        
        
           
     
     
    
  <table border="0" width="100%"><tbody> <tr> <th class="admin_heading_th" width="9%">
</th><th class="admin_heading_th" align="middle" width="20%">User Full Name</th><th class="admin_heading_th" align="middle" width="13%">Client IP</th><th class="admin_heading_th" align="middle" width="15%">Time Logged-in</th><th class="admin_heading_th" align="middle" width="28%">Logged-on to</th></tr><tr> <td class="admin_info_td" width="9%"> 
         [U]Kick[/U] 
        </td><td class="admin_info_td" align="left" width="20%">Hannes Van der Merwe </td><td class="admin_info_td" align="left" width="13%">172.16.11.61</td><td class="admin_info_td" align="left" width="15%">2010-03-10 15:22:13.0</td><td class="admin_info_td" align="left" width="28%">mtcolifants</td></tr> <tr> <td class="admin_info_td" width="9%"> 
         [U]Kick[/U] 
        </td><td class="admin_info_td" align="left" width="20%">Harry Bothma </td><td class="admin_info_td" align="left" width="13%">192.168.32.22</td><td class="admin_info_td" align="left" width="15%">2010-03-10 15:13:09.0</td><td class="admin_info_td" align="left" width="28%">mtcolifants</td></tr> </tbody> </table>
 <form name="inputForm" action="adminServlet" method="post">
  <input name="action" value="loginKick" type="hidden"> 
  <input name="userName" value="" type="hidden"> 
 </form>

Thank you for your help and if I made my post incorrectly I apologize.

John_w · Mar 10, 2010

Set LoginForm = doc.forms(0) the variable LoginForm = Nothing while when trying the original code it gives me LoginForm = "[object]".

It's not easy to analyse your HTML the way you've posted it, but it looks like there is only one form so doc.forms(0) should work. Post the HTML source code again, this time between [ HTML ] and [ /HTML ] tags (without the spaces) and then I'll be able to create a local .html file from it.

ocnuybear · Mar 10, 2010

Hi John_w,

Sorry about that, here is the source code again - thanks for your fast reply:

HTML:

"http://www.w3.org/TR/html4/loose.dtd">

******** language="javascript" type="text/javascript">
    // Back button/link
    function doBack() {
        history.back(1);
    }      

        // Back button/link
    function back() {
        history.back(1);
    }      

    // Delete item function uses generic form as name of form to submit
    function confirmDelete(action,id) {
        if (confirm("The user will be deleted permanantly, do you want to continue?")) {
            document.form.oid.value=id;
            document.form.action.value=action;
            document.form.submit();
        }
    }

     function confirmItemDelete(action,id) {
        if (confirm("The Item will be deleted permanantly, do you want to continue?")) {
            document.form.itemOid.value=id;
            document.form.action.value=action;
            document.form.submit();
        }
    }

    // confirms kickiing/logging out of user from system
    function confirmUserKick(formName, oidValue)  {
        if (confirm("Are you sure you want to LOGOUT this user? ")) {
            var theForm = document.getElementById(formName);
            var userName = document.getElementById('userName');
            theForm.userName.value = oidValue;
            theForm.submit(); 
            return true;
     } else
            return false;
    }
    
    // generic search popup window
    function search() {
        var searchtext = prompt("Please enter the name of the user to search for");
        if (searchtext!=null && searchtext!="") {
            document.form.searchtext.value=searchtext;
            document.form.action.value='searchtext';
            document.form.submit();
        }                
    }
    
    // page refresh
    function refresh() {
        document.form.searchtext.value="";
        document.form.submit();
    }

   // form submit
    function submitFormNoParams() {
        document.form.submit();
    }
    
   // form submit
    function submitFormWithActionParam(action) {
        document.form.action.value=action;
        document.form.submit();
    }     
    
    // form submit
    function submitForm(bname,task) {
        var bname=document.getElementById(bname);  
        bname.disabled='true';
        var action = document.getElementById('action');  
        action.value = task;  
        document.form.submit();
    }      

    function pageLoading() {
        if (document.getElementById) {  // DOM3 = IE5, NS6
            document.getElementById('hidepage').style.visibility = 'hidden';
        }else {
            if (document.layers) {  // Netscape 4
                document.hidepage.visibility = 'hidden';
            } else {  // IE 4
                document.all.hidepage.style.visibility = 'hidden';
            }
        }
    }
       function PopUpWindow(url,windowname,width,height,scroll,rez,tool,menu,loc,horz,vert)     {
            if(vert=='top')  {
                wint = 0;
            }

            if(vert=='middle') {
                wint = (screen.height - height) / 2;
            }

            if(vert=='bottom') {
                wint = (screen.height - height);
            }

            if(horz=='center') {
                winl = (screen.width - width) / 2;
                popupwindow = 
                ************(url,windowname, 

'width='+width+',height='+height+',screenX='+winl+',screenY='+wint+',left='+winl+',top='+wint+',scrollbars='+scroll+',resizable=

'+rez+',toolbar='+tool+',menubar='+menu+',location='+loc);
                popupwindow.focus();
            }
            else {
                if(horz=='left')  {
                    var winl = 0;
                    popupwindow = 
                    ************(url,windowname, 

'width='+width+',height='+height+',screenX='+winl+',screenY='+wint+',left='+winl+',top='+wint+',scrollbars='+scroll+',resizable=

'+rez+',toolbar='+tool+',menubar='+menu+',location='+loc);
                    popupwindow.focus();
                }

                if(horz=='right') {
                    winl = (screen.width - width);
                    popupwindow = 
                    ************(url,windowname, 

'width='+width+',height='+height+',screenX='+winl+',screenY='+wint+',left='+winl+',top='+wint+',scrollbars='+scroll+',resizable=

'+rez+',toolbar='+tool+',menubar='+menu+',location='+loc);
                    popupwindow.focus();
                }
            }
        }
        
*********>
   
     ******** language="JavaScript1.2">
      function focusUN() {
            document.loginform.userName.focus();
     }
  *********>
 
















[IMG]http://www.mrexcel.com/forum/images/ele.jpg[/IMG]
[B]Olifants (b20091013) [/B]




    
        
        
            
        
        
            
        
        
            
        
    User NamePassword



type="reset" value="Cancel" *******="focusUN()">





******** language="JavaScript1.2">
     document.loginform.userName.focus();
*********>



  
    
  
 Current online users: List 
    


  
    
    
      
      
      
      
      
    
    
       
        
        
        
        
           
     
       
        
        
        
        
           
     
     
    
    
User Full NameClient IPTime Logged-inLogged-on to  
         Kick 
        Hannes Van der Merwe 172.16.11.612010-03-10 15:22:13.0mtcolifants   
         Kick 
        Harry Bothma 192.168.32.222010-03-10 15:13:09.0mtcolifants

John_w · Mar 10, 2010

Nope, that doesn't look like HTML. Are you going to http://mtcolifants/faces/loginProper.jsp, right-clicking and viewing source? If the login form is part of a frame make sure you're looking at the frame's source, not the frameset source - Firefox is better than IE for this.

Also, with the page being .jsp this whole thing might not work.

Excel VBA to Submit UserName & Password on IE

BPtexan

New Member

Excel Facts

Norie

Well-known Member

BPtexan

New Member

Norie

Well-known Member

John_w

MrExcel MVP

BPtexan

New Member

ocnuybear

New Member

John_w

MrExcel MVP

ocnuybear

New Member

John_w

MrExcel MVP

Similar threads

Forum statistics

Share this page

Excel VBA to Submit UserName & Password on IE

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock