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