Excel VBA to Submit UserName & Password on IE

B

BPtexan

New Member
Joined
Jul 4, 2009
Messages
24
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
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.
 
Upvote 0
Is there any reason why you want/need to use Excel VBA?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Set LoginForm = doc.forms(0) the variable LoginForm = Nothing while when trying the original code it gives me LoginForm = "[object]".
Click to expand...
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
How to login with username and password via Microsoft Edge in VBA code
Replies
1
Views
2K
healeydm
H
S
  • Solved
VBA scrapping of USPTO website
2 3
Replies
28
Views
2K
Domenic
D
P
  • Question Question
Password Expires After 6 Months
Replies
1
Views
650
Micron
M
C
  • Question Question
VBA to wait until IE webpage finishes loading before running the next step
Replies
3
Views
6K
cdalgorta
C
J
  • Question Question
Login to a website using a macro and then click a button
Replies
3
Views
4K
jeremypyle
J

Forum statistics

Threads
1,225,284
Messages
6,184,049
Members
453,207
Latest member
mnmgal

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top