Alex wants to know how to login into a website automatically using Excel VBA. His email is reproduced below:
‘Hi Mr.Takyar,
Thank you for providing such valuable tutorials for excel.
I was wondering if there is a way this task can be accomplished.
I’m trying to access a website (URL) that requires username and password.
I have to enter my credentials every time I access this site. Is there a way to create a macro or VBA that I can run to make the process quicker? If so can you please demonstrate how I can get this accomplished. I sincerely appreciate your help.
Alex’
He wants to automate the process of logging into a website automatically using Excel VBA. He has the username and password for the website. Our video demonstrates how you can login automatically into a website like Gmail using Excel VBA:
1. Go to the Gmail website
2. View its source code to find out the labels used for the username and password
3. Start the Visual Basic Editor in MS Excel
4. Activate the following two items by clicking on Tools and then ‘References…’:
•Microsoft Internet Controls
•Microsoft HTML Object Liabrary
5. Next define two public variables
6. Now start writing the macro
7. Using a looping process access the username and password text-boxes automatically for entry of the relevant data
8. Then identify the ‘submit’ button, click on it and exit the for loop
9. To avoid error messages popping up write a few lines of code to ignore the errors
You’ll notice that the VBA code given below works well:
Dim HTMLDoc As HTMLDocument
Dim MyBrowser As InternetExplorer
Sub MyGmail()
Dim MyHTML_Element As IHTMLElement
Dim MyURL As String
On Error GoTo Err_Clear
MyURL = “https://www.gmail.com”
Set MyBrowser = New InternetExplorer
MyBrowser.Silent = True
MyBrowser.navigate MyURL
MyBrowser.Visible = True
Do
Loop Until MyBrowser.readyState = READYSTATE_COMPLETE
Set HTMLDoc = MyBrowser.document
HTMLDoc.all.Email.Value = “[email protected]” ‘Enter your email id here
HTMLDoc.all.passwd.Value = “abc+123” ‘Enter your password here
For Each MyHTML_Element In HTMLDoc.getElementsByTagName(“input”)
If MyHTML_Element.Type = “submit” Then MyHTML_Element.Click: Exit For
Next
Err_Clear:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub
Watch the video:
How to do this in Firefox?please help
Use the path to your Firefox program like this: Shell (“C:Program Files (x86)Mozilla Firefoxfirefox.exe
Of course, this is the path on my computer.
Can i know which path Shell (“C:\Program Files (x86)\Mozilla Firefox\firefox.exe should i input it?
This one?
Dim MyBrowser As InternetExplorer
Set MyBrowser = New InternetExplorer
You have to locate the file firefox.exe on your computer and use that path.
Hello Sir
can you please give me a code to do the same to login and password into chrome
Hi Sir, I am trying to run this for my Gmail ID; it works all good till putting in the email and password. But, it stops there, doesn’t automatically click on the ‘sign in’. It is very urgent sir, I am working on a project that needs to be submitted in 4 days. Please help.
Check the html code of the ‘sign in’ and use the correct value or name.
But in this case, we are not entering any value; we are clicking in so how can this work. Can you guide mr dinesh. Clicking code has to be different
Hi Dinesh Sir
I am too facing same issue.. i have checked ‘Sign In ‘ everything is fine but still facing same issue.
Can you please fix this problem???thanks in advance..
How Can i use this macro for logins multiple sites in single excel sheet
How do we use this to convert a worksheet to a html file and then up load it to our web site?
Thank you Dinesh.
How to Login Income Tax web site through excel & i want to add more than one.
Thanks in Advance.
Dear sir
It shows a compile error
the below lines of code is highlighted in “red” when i paste it please help
MyURL = “https://www.gmail.com”
HTMLDoc.all.Email.Value = “[email protected]” ‘Enter your email id here
HTMLDoc.all.passwd.Value = “abc123″ ‘Enter your password here
I am also getting same error, please help me if you got solution.
This is because of the “” used in the code. Please remove the “” written in code and replace it manually in your system. The code has the “” in other format. I hope this will help u.
How can i browse and login to a website using firefox, in IE it is working fine but i want it in Mozilla as Application is compatible with mozilla.. Please help here
Nice code !!
But I can do to open in url than email. (other non-email)
for example this page,
https://www.exceltrainingvideos.com/how-to-login-automatically-into-website-using-excel-vba/
or other blogs
I guest
I need username and password.
In the Excel would have to A2: the url, B2: user name, C2: key (this because eventually can change it). I click on a command button and automatically open my page. As I can do this with Excel macro code?
Thank you
how to click on log out button if the source code is like:
““
Hi Thanks for creating such useful macros and I need a micro for auto web login and I was try this code but unfortunately its not working its showing error at time of Compile in bellow mention line.
HTMLDoc.all.Email.Value = “[email protected]” ‘Enter your email id here
HTMLDoc.all.passwd.Value = “abc+123” ‘Enter your password here
I think my system is not recognize (Email) function so please suggest in this error msg.
I really appreciate your effort.
Thank You SO much.
Hi Sir,
I need your help everything is working fine in above code but its not submitting for login so need your help its Opening Website – Entering ID & Password but its not clicking on login button and I was also try by website source code so please help me out.
Thanks in Advance 🙂
Hi sir, will this also works for othwr fields? like address or age.
There is Compile error that :-
User-Defined type not identified
just go to Tools > References > check in for 1.Microsoft HTML Object Library 2. Microsoft Internet Controls
Hello Sir,
How do I clear the existing data present under Email address box and enter new data .
For example, I’m working on Automating and raising a Ticket and I’ve saved a Quick link for that.
The quicklink for that ticketing website isn’t being saved unless I enter some text in it.
So, I wrote “test” under description section of the Ticket. I’m trying to pull the data from Excel Cells, and pasting it under description.
But the text isnt being pasted under description section and it says “test “even after macro is run successfully. I’ve inspected the element and I’m looking at correct section.
However, any data isn’t being pasted in the other boxes of the ticket as well. Please advise sir, below is my code:
Dim HTMLDoc As HTMLDocument
Dim MyBrowser As InternetExplorer
Sub TT()
Dim MyURL As String
On Error GoTo Err_Clear
MyURL = ” CONFIDENTIAL SITE”
Set MyBrowser = New InternetExplorer
MyBrowser.Silent = True
MyBrowser.navigate MyURL
MyBrowser.Visible = True
Do Loop Until MyBrowser.readyState = READYSTATE_COMPLETE Set HTMLDoc = MyBrowser.document HTMLDoc.all.short_description.Value = “TIGER” HTMLDoc.all.Details.Value = “LION” For Each MyHTML_Element In HTMLDoc.getElementsByTagName(“input”) If MyHTML_Element.Type = “submit” Then MyHTML_Element.Click: Exit For Next Err_Clear: If Err 0 Then Err.Clear Resume Next End If End Sub
I am trying to get this to work . So far with your code I am able to get the uid and pw typed into the page correctly but cant click the sign in button.
Here is the html from the page.
Sign In
And here is my code
Dim HTMLDoc As HTMLDocument
Dim MyBrowser As InternetExplorer
Sub MyBoa()
Dim MyHTML_Element As IHTMLElement
Dim MyURL As String
On Error GoTo Err_Clear
MyURL = “https://www.bankofamerica.com/”
Set MyBrowser = New InternetExplorer
MyBrowser.Silent = True
MyBrowser.navigate MyURL
MyBrowser.Visible = True
Do
Loop Until MyBrowser.readyState = READYSTATE_COMPLETE
Set HTMLDoc = MyBrowser.document
HTMLDoc.all.onlineId1.Value = “myuidwashere”
HTMLDoc.all.passcode1.Value = “mypwwashere”
For Each MyHTML_Element In HTMLDoc.getElementsByTagName(“input”)
If MyHTML_Element.Type = “Sign in” Then MyHTML_Element.Click: Exit For
Next
Err_Clear:
If Err 0 Then
Err.Clear
Resume Next
End If
End Sub
Any ideas I would love to get this to function fully!
let me try the html code again since it ran the code instead of showing it.
button id=”hp-sign-in-btn” class=”hp-sign-in-btn btn-bofa btn-bofa-small btn-bofa-noRight btn-bofa-blue” data-cm='[“homepage_sign_in”, “homepage:Content;home_personal”]’>Sign In</button
Hi,
I have tried to use the same code for different web page , all datas on entered automatically but submit button doesnot working. Here is my code
Dim HTMLDoc As HTMLDocument
Dim MyBrowser As InternetExplorer
Sub Test()
Dim MyHTML_Element As IHTMLElement
Dim MyURL As String
On Error GoTo Err_Clear
MyURL = “xxxxx”
Set MyBrowser = New InternetExplorerMedium
MyBrowser.Silent = True
MyBrowser.navigate MyURL
MyBrowser.Visible = True
Do
Loop Until MyBrowser.readyState = READYSTATE_COMPLETE
Set HTMLDoc = MyBrowser.document
HTMLDoc.all.txtUserName.Value = “xxxxxx”
HTMLDoc.all.txtPassword.Value = “xxxxx”
HTMLDoc.all.drpDomain.Value = “xxxxx”
For Each MyHTML_Element In HTMLDoc.getElementsByTagName(“btnLogon”)
If MyHTML_Element.Type = “submit” Then MyHTML_Element.Click:
Exit For
Next
Err_Clear:
If Err 0 Then
Err.Clear
Resume Next
End If
End Sub
Pl advise
What worked for me was using
HTMLDoc.getElementsByName
instead of
HTMLDoc.getElementsByTagName
When I trying this for chrome : I encounter a error :
My code
Dim HTMLDoc As HTMLDocument
Dim MyBrowser As WebBrowser
Sub MyGmail()
Dim MyHTML_Element As IHTMLElement
Dim MyURL As String
Dim ChromePath As String
On Error GoTo Err_Clear
‘MyURL = “https://www.google.com”
Set MyBrowser = New WebBrowser
MyBrowser.Silent = True
ChromePath = “C:\Program Files (x86)\Google\Chrome\Application\chrome.exe”
Shell (ChromePath & ” -url ” & MyURL)
MyBrowser.Visible = True
Do
Loop Until MyBrowser.readyState = READYSTATE_COMPLETE
Set HTMLDoc = MyBrowser.document
HTMLDoc.all.Search.Value = “Ravi”
HTMLDoc.all.btnG.Click
Err_Clear:
If Err 0 Then
MsgBox Err.Description
Err.Clear
Resume Next
End If
End Sub
Hi Respected Danish Sir!
I am trying to log in gmail but now login procedure is changed. first we need to enter email then press next and then password.
kindly let me change in VBA code
can somebody answer why the code doesn’t work to click submit
Hello, How can i modify this code using ms access instead excel. Thank You
I need your help to have solution regarding a query that I have User name in column C, Password in column D and website link in column E…….so I need, that website should be opened as I click on column E and user id and password should be auto entered at that place where it requires on the website. I am attaching the model data file with 3 model rows, the data may increase. so please help me.
Your help will simplify my work..Pls send me it to my mail id. Thanks a lot
Syntex error is coming in my program my code is:
Dim HTMLDoc As HTMLDocument
Dim MyBrowser As InternetExplorer
Sub MyGmail()
Dim MyHTML_Element As IHTMLElement
Dim MyURL As String
MyURL = “shorturl.at / oqHV2”
Set MyBrowser = New InternetExplorer
MyBrowser.Silent = True
MyBrowser.navigate MyURL
MyBrowser.Visible = True
Do
Loop Until MyBrowser.readyState = READYSTATE_COMPLETE
Set HTMLDoc = MyBrowser.document
HTMLDoc.all.email.value = “[email protected]” ‘Enter your email id here
HTMLDoc.all.passwd.Value = “abc+123” ‘Enter your password here
On Error Resume Next
For Each MyHTML_Element In HTMLDoc.getElementsByTagName(“input”)
If MyHTML_Element.Type = “submit” Then MyHTML_Element.Click: Exit For
Next
End Sub
Hi,
Iam using your code to extract data from my intranet web application to excel. It is a .iql query. It’s working exactly as expected.
But the problem is, after saving the file, I close it and reopen. Now if click “Refresh all” data is not refreshed. Exciting data wiped off and some error appears.
If I click “Data”->”From Web” once and login to my site again close the window and click “Refresh All” then it is working fine. Please helpe to avoid this step. I using your code on a click of a vba button.
Thanks in advance.
Thank you so much sir.
Hi sir,
I have a query. I referenced your code to enter the username and password details in a SSO website(after hitting the initial url it takes to Secured page for login). But the code doesn’t identifies the element and comes out of the loop. Could you please help in this regard.
Sir can I make similar type of application to track couriers coming via different couriers such as TCI express, DTDC etc.
I have the docket no.s in an excel sheet and I want to automatically track them andoutput the current status in an excel cell. Is it possible Sir ??
how to set mybrowser as firefox or chrome or miscrosoft edge etc?
so it open in other browser too
https://www.exceltrainingvideos.com/open-any-third-party-application-using-shell-function/