none
VBA Loggin into secure websites RRS feed

  • Question

  • To keep this short, I have tried for days, used many codes, manipulated many fields to try to get a VBA code to:

    1 Open a web browser from an excel macro (i.e. yahoo.com,facebook.com)

    2 Input the Username and Password fields ( Some websites have them on different pages ; i.e. username page 1 click submit, page 2 password click submit)

    3 leave the page open, I will close it.

    From what I understand I viewed the webpage source code and from bbt.com I got the User ID field to be 'usernamefield' and the password field to be 'userpw'

    I have tried to get many codes that generous people uploaded to the web to work but I am failing miserably.  For this code it fails at this line:

    ((  UserIdBox.Value = cUserID  ))

    Thank you for your time.

    Option Explicit
    
    Sub Test()
        
        Const cURL = "americanexpress.com"
        Const cUserID = "race"
        Const cPwd = "car"
        
        Dim ie As Object
        Dim doc As HTMLDocument
        Dim PageForm As HTMLFormElement
        Dim UserIdBox As HTMLInputElement
        Dim PasswordBox As HTMLInputElement
        Dim FormButton As HTMLInputButtonElement
        Dim Elem As IHTMLElement
        
        Set ie = CreateObject("InternetExplorer.Application")
        
        ie.Visible = True
        ie.navigate cURL
        
        Do While ie.Busy Or Not ie.readyState = IE_READYSTATE.complete: DoEvents: Loop
               
        Set doc = ie.document
        
        Debug.Print "Login page: " & ie.LocationURL
        For Each Elem In doc.all
            
        Next
        
        Set PageForm = doc.forms(0)
              
        Set UserIdBox = PageForm.elements("usernamefield")
        
        UserIdBox.Value = cUserID
        
        Set PasswordBox = PageForm.elements("userpw")
        
        PasswordBox.Value = cPwd
        
        PageForm.submit
        
        Do While ie.Busy Or Not ie.readyState = IE_READYSTATE.complete: DoEvents: Loop
        
        Set doc = ie.document
        
        Debug.Print "Terms of Use page: " & ie.LocationURL
        For Each Elem In doc.all
            
        Next
        
        Set PageForm = doc.forms(0)
    
        Set FormButton = PageForm.elements("selection")
        FormButton.Click
        
        Do While ie.Busy Or Not ie.readyState = IE_READYSTATE.complete: DoEvents: Loop
        
        Set doc = ie.document
        
        Debug.Print "Main Pronto page: " & ie.LocationURL
        For Each Elem In doc.all
            
        Next


    Monday, January 7, 2013 3:55 PM

All replies

  • This code may help you in your quest. 

    Dim objIExplorer As InternetExplorer
    Public Sub Main()
        'Reference set for: 'Microsoft Internet Controls'
        
        Set objIExplorer = CreateObject("InternetExplorer.Application")
        
        'Disable pop-up messages
        objIExplorer.Silent = True
        
        'Arrange Internet Explorer on the screen and make visible
        objIExplorer.Top = 0
        objIExplorer.Left = 0
        objIExplorer.Height = 1000
        objIExplorer.Width = 1250
        objIExplorer.Visible = True
        
        objIExplorer.Navigate "www.google.com"
        Do While objIExplorer.Busy Or Not objIExplorer.ReadyState = 4: DoEvents: Loop
        objIExplorer.Document.getElementById("q").Value = "Hi"      'Set the value in the Inputbox
        
    End Sub

    Good Luck
    Monday, January 7, 2013 8:04 PM
  • That works nicely.  Thank you for getting me the farthest I have been with the code.

    Now just trying to figure why the .click command is not working.

    Do you have to alter the code for different websites other then the "ID's"?????

    Wednesday, January 9, 2013 4:01 AM
  • I have modified the code to include clicking a button. 'gbqfba' is actually the Id for the Google Search button.  Go figure.

    Dim objIExplorer As InternetExplorer
    Public Sub Main()
        'Reference set for: 'Microsoft Internet Controls'
        
        Set objIExplorer = CreateObject("InternetExplorer.Application")
        
        'Disable pop-up messages
        objIExplorer.Silent = True
        
        'Arrange Internet Explorer on the screen and make visible
        objIExplorer.Top = 0
        objIExplorer.Left = 0
        objIExplorer.Height = 1000
        objIExplorer.Width = 1250
        objIExplorer.Visible = True
        
        objIExplorer.Navigate "www.google.com"
        Do While objIExplorer.Busy Or Not objIExplorer.ReadyState = 4: DoEvents: Loop
        objIExplorer.Document.getElementById("q").Value = "Hi"          'Set the value in the Inputbox
        x = objIExplorer.Document.getElementById("gbqfba").sourceindex  'Get the index for the Google Search button
        objIExplorer.Document.all.Item(x).Click                         'Click the Search button
        
    End Sub

    Wednesday, January 9, 2013 5:34 PM
  • Thank you for helping me.  I have been able to tackle about 20 logins so far.  Before I ask two more questions I would like you to know that I have extensive knowledge of Solidworks and 3DviaComposer.  If by chance you use these programs or maybe need a small model done I could help.  I hate to be that annoying person that keeps asking and asking but I tried for hours to figure out these two questions last night and I feel since you know coding so well it would take you a few minutes probably less.  Even though this is probably very simple for you in the end it will save me so much time and energy making my day's less stressful.

    1.  What is the ID for the Logon button for www.bbt.com ? I have it as "bbt-logon" but the screen just sits there after it inputs my username ( using your last code)

    2.  Why does the user Id field get skipped on the code below for americanexpress.com

    'Dim objIExplorer As InternetExplorer
    Public Sub bbt()
        'Reference set for: 'Microsoft Internet Controls'
        
        Set objIExplorer = CreateObject("InternetExplorer.Application")
        
        'Disable pop-up messages
        objIExplorer.Silent = True
        
        'Arrange Internet Explorer on the screen and make visible
        objIExplorer.Top = 0
        objIExplorer.Left = 0
        objIExplorer.Height = 1000
        objIExplorer.Width = 1250
        objIExplorer.Visible = True
        
        objIExplorer.navigate "www.americanexpress.com"
        Do While objIExplorer.Busy Or Not objIExplorer.readyState = 4: DoEvents: Loop
        objIExplorer.document.getElementById("userid").Value = "username"  'Set the value in the Inputbox
        objIExplorer.document.getElementById("Password").Value = "password"
        x = objIExplorer.document.getElementById("loginimage").sourceIndex  'Get the index for the Google Search button
        objIExplorer.document.all.Item(x).Click
        
    End Sub

    Thursday, January 10, 2013 4:30 PM
  • Just interested to know why you are using VBA and not some easier solution.
    Thursday, January 10, 2013 5:11 PM
  • What solution would that be?  I'm a mechanical engineer and a carpenter.  This coding stuff is on the bottom of my skill set.  What is an "easier solution"
    Thursday, January 10, 2013 6:40 PM
  • I'm an engineer, too, and a hobbyist carpenter.  I'm sure you will understand when I say that Excel VBA is my "hammer" so all problems look like "nails" to me - go with what you know, what you are comforatable with, and what will work.
    Thursday, January 10, 2013 6:47 PM
  • Well right now, thanks to BigMeanCat, the VBA code is working great.  Just a few websites I am having problems with.  I wish there was a vba recorder when you navigated and did things on the websites and you just copied the code at the end.  I would think there would be one but all I found was HTML recorders.
    • Edited by VBA_Noob Thursday, January 10, 2013 7:36 PM
    Thursday, January 10, 2013 7:05 PM
  • you can download rediffmail sender from send email from excel using rediffmail account

    I think it is good reference you are looking for


    Nasir Akhtar

    Friday, January 11, 2013 6:05 PM
  • Nasir, I don't need to send emails from excel.  I did try to view the code to help me but it's password protected.  I need to be able to logon to accounts.  I appreciate you trying to help me though.

    Does anyone know why this code skips the UserID field?
    Public Sub bbt()
            
        Set objIExplorer = CreateObject("InternetExplorer.Application")
        
        objIExplorer.Silent = True
        
        objIExplorer.Top = 0
        objIExplorer.Left = 0
        objIExplorer.Height = 1000
        objIExplorer.Width = 1250
        objIExplorer.Visible = True
        
        objIExplorer.navigate "www.americanexpress.com"
        
        Do
            If objIExplorer.readyState = 4 Then
                objIExplorer.Visible = True
                Exit Do
            Else
                DoEvents
            End If
        Loop
        
        objIExplorer.document.getElementById("userid").Value = "username"
        objIExplorer.document.getElementById("Password").Value = "password"
        x = objIExplorer.document.getElementById("loginimage").sourceIndex
        objIExplorer.document.all.Item(x).Click
        
    End Sub
    
    


    Saturday, January 12, 2013 4:16 AM
  • I use iMacros to automate logins.  iMacros is a free addon in Firefox, IE and Chrome.  I have yet to find a website I can't automate and it only takes a few minutes to do.
    Sunday, January 13, 2013 3:20 PM
  • I use IMacros too but it records in HTML and I need it in VBA for Excel and SW. 
    Sunday, January 13, 2013 10:27 PM
  • Finding the “Id” for an element on a web page is a little process.

    For American Express

    Run the code below. Do not exit the procedure when it reaches “Stop”.
    On the web page put the cursor in the “UserId” box so it is the active element.
    In the Visual Basic Editor highlight “objIExplorer”
    Enter the Key combination Shift + F9
    Press “Enter”. The Watches window will open.
    Navigate to “objIExplorer.Document.ActiveElement”
    Locate “id” and that is the value you are looking for.

    To get the “id” for the button on the bbt site.
    Do the same as above but change the code to navigate to www.bbt.com
    On the web page put the cursor in the “UserId” box so it is the active object.
    Then click the Tab key to make the LOGON button active.
    Return to the VBE and do the same as above.

    Dim objIExplorer As InternetExplorer
    
    Public Sub Main()
        'Reference set for: 'Microsoft Internet Controls'
        
        Set objIExplorer = CreateObject("InternetExplorer.Application")
        
        'Disable pop-up messages
        objIExplorer.Silent = True
        
        'Arrange Internet Explorer on the screen and make visible
        objIExplorer.Top = 0
        objIExplorer.Left = 0
        objIExplorer.Height = 1000
        objIExplorer.Width = 1250
        objIExplorer.Visible = True
        
        objIExplorer.Navigate "www.americanexpress.com"
        Stop
        
        objIExplorer.Quit
        Set objIExplorer = Nothing
    End Sub
    

    Monday, January 14, 2013 11:00 PM
  • Thanks for replying again BigMeanCat.  Your the only one on this forum so far that knows this code and what needs to be done.

    I followed your instructions and did learn this new technique to find the ID's and some future helping things, however the ID for User ID for www.americanexpress.com is "userid" which I have been using and its skipping it even with your wait till load script before it.

    The Logon button for BBT is said to be, "".  I said what the hell and put that into the code but it errored out at that line.  I had been using "logon" and "bbt-logon" and many others.

    It's frustrating because I can log onto many websites thanks to you.  Why are these being so difficult.  There are others too, but I am hoping it will trickle down with whatever is going wrong here.

    Thanks Again
    Tuesday, January 15, 2013 12:36 AM
  • Hi VBA_Noob,

    The problem with the User ID input for American Express is that the id: "UserID" is used for two elements on the web page. 

    If more than one element is found, getElementById returns the first object in the collection.

    Reference:  http://msdn.microsoft.com/en-us/library/ie/ms536437%28v=vs.85%29.aspx

    You could use the sourceindex value as such.

    objIExplorer.Document.all.Item(495).Value = "Username"

    Where 495 is the sourceindex for the User ID box.

    The potential problem here is that sourceindex values change when changes are made to the web site.

    Hope this helps.

    ‘*********************************************************************************

    By the way,

    I have not been able to reply to threads in this forum for about a week+.  I keep getting the Error:  Message from webpage / Unexpected error

    If you don't here from me that's because I cannot send replies.

    Thanks,

    BMC

    Sunday, January 20, 2013 6:39 PM
  • I officially give up.  This is to frustrating.  Don't understand why this process has to be so hard. 
    Thursday, January 24, 2013 3:16 AM
  • I do a lot of web scrapping and I use iMacros Enterprise which provides a very easy method to do secure logins and do web scrapping.  It has a very easy to use programming interface.  I do it with both C# and VBA.   I bought it back when it was 1/3 the price it is today. 
    Friday, January 25, 2013 9:13 PM
  • IMACROS is great and I have the free addin for firefox but I want the macros on buttons on my excel sheet for ease of access.  I have started the task today of making the 28 accounts into buttons.  You really have to be careful of your finances.  Last year I had over $300.00 in fraud to my accounts and just today I found out $40.00 was withdrawn out my account that wasn't me or my wife.  The signature is David something and my name is Scott and my wife is Beth and the bank just let the man/women take the money.  There are double charges from companies and subscriptions thru others or "one time" access fees.  It's disgusting. 

    My excel sheet has taken me months of coding in the sheet and with the help of BigMeanCat with macros to access all these 28 accounts.  It has become a whole lot easier to do.

    I don't like the idea of inputting all of your personal account information into Mint.com or quick books.  I am good at Math and honestly my excel sheets do the exact same thing theirs do and more.  I also enjoy the customization of using excel.

    What do you guys use???  Below is the start of inserting buttons but I am hungry now and the wifey want's to go out.

    Thanks to everyones help especially BigMeanCat, and Cat I feel like I have asked you too many questions but if you have time, I don't understand how to find the information of

    {{{ objIExplorer.Document.all.Item(495).Value = "Username"

    Where 495 is the sourceindex for the User ID box. }}}}


    Saturday, January 26, 2013 11:26 PM
  • <form action="https://online.bbt.com/auth/pwd.tb" method="post">
    <input name="UserName" type="text" class="UserID" id="usernamefield">
    <button type="submit">LOGON</button>
    <input value="Go" name="input" type="hidden">
    </form>

    This is the form that you're trying to submit from http://www.bbt.com/. I've simplified the HTML a bit to remove stuff that isn't important. They have a "onsubmit" function declared; I checked their .js includes and all this function does is check that a username is entered, and calls .submit if there is one. It was quite possible that this function manipulated other stuff before posting the form. Luckily, it done very little.

    Most the elements of their form have no ID fields or name fields, which is why you're finding it hard to refer to them in code.

    However, there is a *much much* simpler way to get past this page. The form posts the data to https://online.bbt.com/auth/pwd.tb.

    You can manually add the correct form items to the URL and simply open a page with that URL. Their webserver cannot tell the difference between a URL entered in manually, and, the one that would be posted by the form if the user done it themselves. Add a "?" to the end of the post address, take each field in the form and add them to the URL. The "name" field is the one you use when doing this method. Like this:

    https://online.bbt.com/auth/pwd.tb?UserName=test&submit&input=Go

    I tried it without the "&input=Go" and it worked fine, but for whatever reason they've got that hidden field in there.

    In code this will get you past the first page. As to what happens next I've no idea because I have no idea what BB&T is and I don't have an account to see what the password page looks like! I'm using the cUserID variable that you used in your code for the other website is similar to the one you'll want to use here:

    ie.navigate "https://online.bbt.com/auth/pwd.tb?UserName=" & cUserID & "&submit&input=Go"

    Let me know how this much simpler approach works out!

    • Proposed as answer by Vexen_Crabtree Friday, February 8, 2013 9:26 PM
    Sunday, January 27, 2013 3:11 AM
  • 2.  Why does the user Id field get skipped on the code below for americanexpress.com 

    objIExplorer.document.getElementById("userid").Value = "username" 'Set the value in the Inputbox


    The user id field is called: "UserID", with a capital U and capital ID. The ID field is case-sensitive according to W3C documentation, therefore, the other poster who said that the A.E. website had duplicate "UserID" field may be wrong; I can see one field on their page in one form, called "USERID" and another called "UserID".

    Or at least... in I.T. ... it is best to always consider everything to be case-sensitive. That way, problems like these never occur. (Good advice, even if after you try it and find it is not case sensitive in this particular case!)


    • Edited by Vexen_Crabtree Sunday, January 27, 2013 3:33 AM Adding note on W3C specs - just checked.
    • Proposed as answer by Vexen_Crabtree Friday, February 8, 2013 9:26 PM
    Sunday, January 27, 2013 3:22 AM
  • To quickly find the ID of the field, I used the following steps (took me less than a minute - really!)

    1. Opened  https://www.americanexpress.com/ in a browser after copying URL from your code into clipboard.

    2. Made a mental note of some of the plaintext word nearest the elements I was interested in on the web page.

    3. Right click... View Source.

    4. Pressed Ctrl-F, typed "User ID", pressed enter.

    5. Right in front of me, was:

    <input type="text" id="UserID" name="UserID" size="12" maxlength="32" valu.......

    Sunday, January 27, 2013 3:26 AM
  • The code below should work :) hope its not tooooooooooooooooooooooooooooo late.

    Sub AmexLogin()
    On Error GoTo Err_Clear
    sURL = "https://www.americanexpress.com/uk/"
    Set oBrowser = New InternetExplorer
    oBrowser.Silent = True
    oBrowser.timeout = 60
    oBrowser.navigate sURL
    oBrowser.Visible = True
    Do
    ' Wait till the Browser is loaded
    Loop Until oBrowser.readyState = READYSTATE_COMPLETE

    Set htmlDoc = oBrowser.document
    htmlDoc.all.loginID.Value = "username"
    htmlDoc.all.Password.Value = "password"
    SendKeys "{Enter}", True

    ' oBrowser.Refresh ' Refresh If Needed
    Err_Clear:

    If Err <> 0 Then
    Err.Clear
    Resume Next
    End If
    End Sub

    Friday, August 5, 2016 10:14 PM
  • Hey Roti Er,

    I tried out your code with this website and I can get the login and password to populate, however I can't get the macro to click on "Log In". Can you please help me with this? (I know I'm super late lol) Thank you!!

    Private Sub CommandButton1_Click()
     
    On Error GoTo Err_Clear
     sURL = random url
     Set oBrowser = New InternetExplorer
     oBrowser.Silent = True
     oBrowser.timeout = 60
     oBrowser.navigate sURL
     oBrowser.Visible = True
     Do
     ' Wait till the Browser is loaded
     Loop Until oBrowser.readyState = READYSTATE_COMPLETE

     Set HTMLdoc = oBrowser.document
     HTMLdoc.all.vb_login_username.Value = "blah"
     HTMLdoc.all.vb_login_password_hint.Value = "blah123"
     SendKeys "{Login button}", True

     ' oBrowser.Refresh ' Refresh If Needed
    Err_Clear:

     If Err <> 0 Then
     Err.Clear
     Resume Next
     End If
     End Sub

    Monday, March 12, 2018 5:23 PM
  • mrexcel.com

    it wouldn't let me attach the link above sorry

    Monday, March 12, 2018 5:24 PM
  • Hey guys,

    I've been trying to use Excel VBA to log into a website with reCAPTCHA two-factor authentication. 

    I've already put it in the code to open up IE and put input the necessary login information to sign in, but I cant seem to get through the "I'm not a robot" security.

    Is this possible? If so, what code would accomplish this?

    Thursday, July 12, 2018 6:06 PM
  • Hello,

    the SendKeys command does not seem right

    The SendKeys emulates a user pushing the Enter key on the keyboard

    It should be : SendKeys "{Enter}", True

    not what you wrote as there is nothing called "login button" on a keyboard

    Also, I wouldn't be surprised that such web pages are automatically generated on navigate.

    I mean, the web page does not exist and is written by third party code(angular, jquery, whatever) so that id's are always different (to prevent hacking and co)

    We use that kind of tech in my sector (banking) . I had to do a similar thing as yours and what I did is the following:

        Dim objIE As InternetExplorer
        Dim doc As HTMLDocument
        Dim e As HTMLInputElement
        Dim link As Object

    Set objIE = New InternetExplorer
    objIE.Navigate "YourURLhere"

    Set doc = objIE.Document

        Set link = doc.getElementsByTagName("button")
        For Each e In link
            If e.outerText = "Login" Then
                e.Click
                Exit For
            End If
        Next l

    Basically, it searches into the loaded document (the web page) any element that is a button and reads its outerText (the text displayed on the screen) and because we search for a button, it has a click event attached to it.

    kind regards,

    Daniel




    • Edited by Daniel_C_001 Wednesday, February 20, 2019 4:32 PM
    Wednesday, February 20, 2019 4:09 PM