How to interact with HTML pages from Excel

.

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

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

26 Comments

  1. New to excel

    This works perfectly I want to thank u so much..

    Reply
    • Vishwamitra Mishra

      You are welcome !! Any suggestions or feedback are most appreciated.

      Reply
      • PRAVEEN YADAV

        I Want to learn I Macro by VBA. pls suggesst!!!

        Reply
  2. ciscocell

    how would you toggle between a drop down menu?

    Informational

    Reply
    • Vishwamitra Mishra

      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

      Reply
      • ciscocell

        Thank you sir i will give it a try

        Reply
      • thiru

        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

        Reply
  3. ciscocell

    "Only"

    Reply
  4. Sulleej

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

    Reply
    • Vishwamitra Mishra

      Thanks Sulleej for giving me a valuable note !!
      These are not paid Articles. All the articles are written by my own 🙂

      Reply
  5. Raymundo Quillin

    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!

    Reply
  6. Val Demarais

    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

    Reply
  7. Scott

    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…

    Reply
    • Scott

      this seems to be intermittent, sometimes it works, sometimes not…

      strange…

      Reply
  8. Jigar

    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.

    Reply
  9. Joe

    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

    Reply
  10. Dinesh Balendran

    You are great and awesome. Excellent article and greatly appreciate your informative in this subject

    Reply
  11. Grandhe

    I’m getting error like this User-defined type not defined while tried to ren in Excel macros

    Reply
  12. rajiv

    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

    Reply
  13. Praveen Yadav

    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
    +

    Reply
  14. Mike

    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

    Reply
  15. Joshi

    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,

    Reply
  16. Ramakrishnan E

    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?

    Reply
  17. Jerry

    Just in case you’re trying to convert HTML to Markdown, I found a really good application for it: http://bit.ly/2nFXFuc

    Reply
  18. Malin Chandra Deka

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

    Reply

Trackbacks/Pingbacks

  1. Get data / reports from web-based program through VBA? - […] help you to get started: IE (Internet Explorer) Automation using Excel VBA - Excel VBA Templates Welcome to LearnExcelMacro.com…
  2. Excel VBA to copy data from Word document to Excel Workbook - LearnExcelMacro.com - […] work the below code you need to Add the reference of Microsoft Word in your Excel […]
  3. VBA Interact with HTML + WebBrowser - […] original idea from this post came from this another one. But I needed to have it extended to any…
  4. VBA code for sending email from Gmail or yahoo - LearnExcelMacro.com - […] HOW TO INTERACT WITH HTML PAGES FROM EXCEL HOW TO SEND MORE THAN ONE SHEET OF THE WORKBOOK IN…

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest