VBA Loggin into secure websites

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

Answer 1

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

Answer 2

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"?????

Answer 3

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

Answer 4

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

Answer 5

Just interested to know why you are using VBA and not some easier solution.

Answer 6

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"

Answer 7

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.

Answer 8

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.

Answer 9

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

Answer 10

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

Answer 11

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.

Answer 12

I use IMacros too but it records in HTML and I need it in VBA for Excel and SW. 

Answer 13

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

Answer 14

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

Answer 15

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

Answer 16

I officially give up.  This is to frustrating.  Don't understand why this process has to be so hard. 

Answer 17

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. 

Answer 18

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. }}}}

Answer 19

<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!

Answer 20

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!)

Answer 21

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.......

Answer 22

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

Answer 23

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

Answer 24

mrexcel.com

it wouldn't let me attach the link above sorry

Answer 25

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?

Answer 26

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