In this article you are going to learn how to interact with HTML pages from Excel Macro/VBA. Though Excel is not much capable and easy enough to interact with HTML pages and its HTML controls. But up to some extend we can interact with HTML pages by Excel Macro.
To interact with HTML pages and its controls we need to add two references in our Excel VBA.
1. Microsoft HTML Object Library (mshtml.tlb): This library is required to access all HTML controls which can be present on your HTML page.
2. Microsoft Internet Controls (ieframe.dll): This reference is required to do operations on Internet Explorer because to open an HTML page we need to access Internet Explorer.
How to Add Reference in Excel
1. Go to VB Editor Screen (Alt+F11)
2. Tools –> References…
3. From the List of Available References Select your Reference Name which you want to add.
Note: If you are not able to find the Reference Name in the list then Click on browse and select the dll name whihc is given for those Reference Names.
4. Click OK
To explain how to open a browser and then a URL and after that recognize some controls on that page and pass some value in it and do some click operation etc, i have taken Gmail Login as an example. In the below example you will see how to login to Gmail from Excel Macro.
Note: Read all the comments gievn in the code, which explains each statement will do what. See if this works for you. If you are facing any issue in this code, do let me know via comment or Mail Me
Private Sub CommandButton1_Click()
Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Dim oHTML_Element As IHTMLElement
On Error GoTo Err
Set oBrowser = New InternetExplorer
oBrowser.Height = 1000
oBrowser.Width = 1000
oBrowser.Silent = False
'oBrowser.tim
oBrowser.navigate "http://gmail.com"
oBrowser.Visible = False
Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE
Set HTMLDoc = oBrowser.Document
'Once browser is fully loaded give few seconds
'this is because sometimes even though the Browser State is
'Complete but still some of the controls are not
'ready completely. In such case your script may fail.
'That's why i have given a waiting time of 3 seconds
'after the page is loaded completely.
Application.Wait DateAdd("s", 3, Now)
'Once browser is open with Gmail URL
'Now we need to pass ID and password at the right
'field. For this right click on the Gmail page
'and click on View Code. Here check the "id" of
'User Name and Password Textboxes.
'For example if ID of the User Name textbox is "usrname"
'then syntax to pass User name in that field would be:
'HTMLDoc.all.usrname.Value="your user name".
'Same way i have passed user name and password as below
HTMLDoc.all.Email.Value = "vishwamitra01"
HTMLDoc.all.Passwd.Value = "*********"
'after entering email id and password
'we need to search the button to Sign in gmail.
'For this also we need to check the ID or name of that
'button by right clicking and seeing the code.
'Once you get the Name of that button then use the
'below code to click on that.
'Here for loop is necessary because if it is not able
'to find the control in first time then it will go
'and look for another button on that page.
'Whichever button it is finiding with the name as
'"signIn", it will click and for loop will end.
For Each oHTML_Element In HTMLDoc.getElementsByName("signIn")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next
Do
Loop Until oBrowser.readyState = READYSTATE_COMPLETE
Application.Wait DateAdd("s", 1, Now)
oBrowser.Visible = True
Exit Sub
Err:
MsgBox ("Error Occured")
End Sub
This works perfectly I want to thank u so much..
You are welcome !! Any suggestions or feedback are most appreciated.
I Want to learn I Macro by VBA. pls suggesst!!!
how would you toggle between a drop down menu?
Informational
Hi Ciscocell…
First you need to get the Type and ID or Name of that drop down. Once you know that then you can toggle between the values of drop down values.
For Example, i have taken example of a website in the below code and it will select a particular value from the drop down.
Hope this helps…
Sub toggle_drop_down()
Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Dim oHTML_Element As IHTMLElement
On Error GoTo Err
Set oBrowser = New InternetExplorer
oBrowser.Silent = False
oBrowser.navigate "http://jobsearch.monsterindia.com/search.html"
oBrowser.Visible = True
Do
Loop Until oBrowser.readyState = READYSTATE_COMPLETE
Set HTMLDoc = oBrowser.Document
Application.Wait DateAdd("s", 3, Now)
For Each oHTML_Element In HTMLDoc.getElementsByName("<name of that Drop Down")
If oHTML_Element.Type = "select-one" Then oHTML_Element.Value = "11": Exit For
Next
End Sub
Thank you sir i will give it a try
Hi Sir
Thank you for your info.
I just need some more help from you – I'm trying to navigate/click to the right options in the gmail that is – inbox, compose, spam, trash etc… but these stuffs are under table I don't know how to do this please help.
Thanks
Thanks
"Only"
I'm really inspired together with your writing talents and also with the structure in your weblog. Is that this a paid topic or did you modify it your self? Either way keep up the nice quality writing, it's uncommon to look a nice weblog like this one nowadays..
Thanks Sulleej for giving me a valuable note !!
These are not paid Articles. All the articles are written by my own 🙂
Have you ever ever considered about including a bit of bit more than just your articles? I mean, what you say is valuable and all. Nonetheless consider if you happen to added some nice pictures or video clips to give your posts extra, "pop"! Your content material is superb but with photos and video clips, this internetsite may undeniably be one of many best in its field. Great blog!
hi
I trying to use this code with a different website and I'm not sure how the syntax for the username and password should work. An extract of the source code below
*
Great if you could please help.
thnx
Hi Vish,
Thanks for your blog, it is very, very helpful…
can you help me with this one though?
I change the above code to access our intranet at work and had it working well, but for some reason (without changing the code) it has stopped working and is now giving me an error (438), can you think of any reason this might be happening?
ps.
I have quite a big project ongoing at the moment and could use some help.
Are your services available for hire?
Regards Scott…
this seems to be intermittent, sometimes it works, sometimes not…
strange…
Hi Mishra,
I have a static HTML File and want to copy the Graphs from that static HTML file to word document. How to do that.
Appreciate your help on this.
Excellent article, very clear. Would appreciate if you could help me on how to click on a link that seems to be set up as Java.
Specifically, I need to automatize the following steps. I was able to program the first three based on your macro, but I got stucked on the fourth one. Thank you very much.
*1. Go to: http://www.crautos.com/usados/economicos-usearch….
*2. Change one or more criteria on the "search table" at the bottom of the page (for instance, selecting Audi on the 'Marca' drop-down list).
*3. Click on "Buscar"
–>4. Click on the first car (either brand, model or year; it's the same).
5. Copy data on "Datos Generales" into Excel
6. Go back (back button of browser)
7. Click on next car
8. Go to step 5
You are great and awesome. Excellent article and greatly appreciate your informative in this subject
I’m getting error like this User-defined type not defined while tried to ren in Excel macros
Hi ,
I am trying to automate a logon procedure.
But each time i navigate to the link, the windows security logon always promots for user id and password
Can anyone pls help me with passing the credentials to the windows security logon prompt using excel marcos
Dear Vishwamitra Sir,
Pls suggesst how to learn ie. Macro.
My Excel VBA is good but not working on Internet Explorer its very critical and urgent for me Sir pls help.
Regards,
Praveen Yadav
+
Sorry if this is really simple but I am new to this.
I am getting user-defined type not defined on “Dim HTMLDoc As HTMLDocument”.
What does this mean?
Thanks in advance
Hi i need help in select the radio button from webpage and click on submit button using Excel macro, any idea how can i write a macro.
Thanks,
Hello Vishwa,
Thanks a lot for the step by step walkthrough on connecting an Excel Macro with HTML. It was very useful.
I have a small clarification with reference to ID of one of the inputs provided in the HTML,
The ID is PJ_5_^_2^1 and when i provide the same in Macro it throws me an excpetion stating Identifier under cursor is not recognized.
What i wanted to do was to assign,
HTMLDoc.all.PJ_5_^_2^1.Value=”1/1/2014″
Can you kindly help me with this?
Just in case you’re trying to convert HTML to Markdown, I found a really good application for it: http://bit.ly/2nFXFuc
https://disqus.com/home/channel/welldabduvul1977spcy2gpwm8/discussion/channel-welldabduvul1977spcy2gpwm8/ati_atombios/
There is spelling mistake in following sentence in the web page
“Note: If you are not able to find the Reference Name in the list then Click on browse and select the dll name whihc is given for those Reference Names.”