Interacting with webpages from within VBA is made simple using Microsoft Internet Controls and the Microsoft HTML Object Library, (ieframe.dll, MSHTML.TLB). To use these libraries you need to add references in your excel macro to these two libraries tools -> references.
Once you have created your browser instance and navigated to a webpage you can then navigate the HTML DOM as you similarly would in other programming languages, eg
Set iHTMLCollection = HTMLDoc.all.tags("div"),
Set iHTMLCollection1 = iHTMLCollection.Item(8).Children.Item(0).Children.Item(1).Children,
If iHTMLCollection1.Item(0).getAttribute("outerHTML") > 0 Then,
If iHTMLCollection1.Item(1).outerHTML Like "*the moggy cat jumped*" The

There is already enough information out there on by Microsoft and via google to add any more VBA basics here, read on for a couple of vba functions related to browsing and the HTML DOM in VBA. 



  1. Set IE = New SHDocVw.InternetExplorer
  2. IE.Visible = True
  3. IE.Navigate ("http://www.openreach.co.uk/orpg/customerzone/loadMyDashboard.do")
  4.  
  5. Do While IE.Busy = True
  6.     'Animate Progress bar
  7. Loop
  8. Do While IE.ReadyState <> 4
  9. Loop
  10.  
  11. Set HTMLDoc = IE.Document
  12. Do While HTMLDoc.ReadyState <> "complete"
  13. Loop

You will need a degree of page verification and also handling server errors, you could account for all possibilities but more likely the individual project will throw up a particular repeat issue to cater for, a 500 Internal Server Error page from an overworked server for example.
In this case it could be as simple as refreshing the page IE.Refresh and carrying on with parsing the HTML DOM. 



  1. 'If we get "500 Internal Server Error" refresh the page and get document again
  2. If HTMLDoc.Title Like "500 Internal Server Error" Then
  3.     IE.Refresh
  4.     Application.Wait (Now() + TimeValue("00:00:" & 3))
  5.     Do While IE.Busy = True
  6.     Loop
  7.     Do While IE.ReadyState <> 4
  8.     'Debug.Print ("'500 Internal Server Error' error fix")
  9.     Application.Wait (Now() + TimeValue("00:00:" & 2))
  10.     Loop
  11. End If

If you intend on interacting regularly with webpages throughout your code a DOM navigation function is nescceary. 




  1. Public Function NavigateHTML(ByVal tag As String, ByVal attr As String, 
  2. ByVal ID As String, ByVal Stext As String)
  3.  
  4.     '########################################
  5.     'tag - Find elements by TagName: 'input' OR 'a' OR 'select... ect
  6.     'attr - Search for a particular Element attribute: 
  7.     '		name, href, type, className ...
  8.     'ID - Specify an identifier to look for in order to confirm element and
  9.     '		 carry out action.
  10.     '	For elements which are of type;
  11.     '		text, password			:Enter text into element
  12.     '		submit, button, reset, image	:Click element
  13.     '		href="*ID*"			:Click on Hyperlink element
  14.     '		checkbox, radio			:Select element
  15.     'Stext - If looking for text/input box specify String to enter, 
  16.     '		or select a certain option.
  17.     '
  18.     'Example usage:
  19.     '
  20.     'Call NavigateHTML("a", "href", "accounts", "")
  21.     '------
  22.     '
  23.     'myUsername = ping
  24.     'myPass = pong
  25.     'Call NavigateHTML("input", "name", "Email", myUsername)
  26.     'Call NavigateHTML("input", "name", "Passwd", myPass)
  27.     'Call NavigateHTML("input", "value", "Sign in", "")
  28.     '------
  29.     '
  30.     'Call NavigateHTML("input", "src", "/includes/confirm.gif", "")
  31.     '------
  32.     '
  33.     'Call NavigateHTML("select", "name", "carManuf", "Skoda")
  34.     '------
  35.     '
  36.     'Call NavigateHTML("input", "className", "actionBtn", "")
  37.     '------
  38.     '##########
  39.     
  40.  
  41.     Do While IE.Busy = True
  42.         'Animate Progress bar
  43.     Loop
  44.     Do While IE.ReadyState <> 4
  45.         'Animate Progress bar
  46.     Loop
  47.  
  48.     Set HTMLDoc = IE.Document
  49.     Do While HTMLDoc.ReadyState <> "complete"
  50.     Loop
  51.     
  52.     Set iHTMLCol = Nothing
  53.     While iHTMLCol Is Nothing
  54.     Set iHTMLCol = HTMLDoc.all.tags(tag)
  55.     Wend
  56.     
  57.  
  58.     Select Case tag
  59.  
  60.     ' Click on hyperlink
  61.     Case "a"
  62.  
  63.         For Each iHTMLEle In iHTMLCol
  64.             If iHTMLEle.getAttribute("outerHTML") <> "" Then
  65.                 If iHTMLEle.outerHTML Like "*" & ID & "*" Then
  66.                     iHTMLEle.Click
  67.                     Stext = "True"
  68.                     Exit For
  69.                 Else
  70.                     Stext = "Could not find Attribute " & tag _
  71.                      & " for element type" & Stext
  72.                 End If
  73.             End If
  74.         Next
  75.  
  76.     ' Select option
  77.     Case "select"
  78.  
  79.         For Each iHTMLSel In iHTMLCol
  80.  
  81.              If iHTMLSel.getAttribute(attr) <> "" Then
  82.                 aStr = iHTMLSel.getAttribute(attr)
  83.                 If aStr = ID Then
  84.                     For Each iHTMLOpt In iHTMLSel.Children
  85.                         If iHTMLOpt.getAttribute("value") = Stext Then
  86.                             iHTMLSel.selectedIndex = iHTMLOpt.Index
  87.                         End If
  88.                     Next
  89.  
  90.                 Else
  91.                     'Stext = "Could not find Attribute " & attr _
  92.                     & " for element type" & ID
  93.                 End If
  94.             End If
  95.         Next
  96.  
  97. 	' Various
  98.     Case "input"
  99.         For Each iHTMLEle In iHTMLCol
  100.             If iHTMLEle.getAttribute(attr) <> "" Then
  101.                 aStr = iHTMLEle.getAttribute(attr)
  102.                 If aStr = ID Then
  103.                     Select Case ID
  104.  
  105.                     ' Click button
  106.                     Case "submit", "button", "reset", "Submit", "Search"
  107.                         iHTMLEle.Click
  108.                         Stext = "True"
  109.                         Exit For
  110.                     
  111.                     ' Enter text into input box
  112.                     Case "text", "password"
  113.                         iHTMLEle.Value = Stext
  114.                         Stext = "True"
  115.                         Exit For
  116.  
  117.                     ' Use Stext to identify the correct checkbox/radio if multiple
  118.                     Case "checkbox", "radio"
  119.                     If Stext = iHTMLEle.getAttribute("name") Or Stext = "" Then
  120.                             If iHTMLEle.Checked = False Then
  121.                                 iHTMLEle.Checked = True
  122.                             Else
  123.                                 iHTMLEle.Checked = False
  124.                             End If
  125.                             Exit For
  126.                         Else
  127.                         End If
  128.                     
  129.                     Case "image"
  130.                         Stext = "No action setup for Tag Image"
  131.                         Exit For
  132.                     
  133.                     Case Else
  134.                         If Stext <> "" Then
  135.                             'Custom form entry
  136.                             iHTMLEle.Value = Stext
  137.                             Stext = "True"
  138.                     
  139.                         ElseIf Stext = "" Then
  140.                             'Custom button click, click the button
  141.                             iHTMLEle.Click
  142.                             Stext = "True"
  143.                             Exit For
  144.                     
  145.                         Else
  146.                             'We should not be here, unkown event
  147.                             Stext = "Unkown Tag: " & tag & ", Attr: " & attr & _
  148.                             	", ID: " & ID & ", Text: " & Stext
  149.                         End If
  150.                         Exit For
  151.  
  152.             End Select
  153.                 Else
  154.                 'Stext = "Could not find Attr: " & attr & " for element: " & ID
  155.                 End If
  156.             End If
  157.         Next
  158. 	End Select
  159.     
  160.     Do While IE.Busy = True
  161.         'Animate Progress bar
  162.     Loop
  163. 	NavigateHTML = Stext
  164.  
  165. End Function

And of course the same goes if your logging into multiple sites, this LogIn function should be called from a LoggedIn function that first checks if you are actually logged into a site, if not this LogIn function should then be called and then the LoggedIn function called again to confirm you are now logged into the site. 

           

  1. Public Function LogIn(ByVal site As String)
  2.     Select Case site
  3.  
  4.         ' Log into google
  5.         Case "google"
  6.  
  7.             'Navigate IE to site
  8.             Call NavigateTo("https://accounts.google.com")
  9.             
  10.             'Login with UN & PASS
  11.             Call NavigateHTML("input", "name", "Email", "myUsername")
  12.             Call NavigateHTML("input", "name", "Passwd", "myPass")
  13.             Call NavigateHTML("input", "value", "Sign in", "")
  14.  
  15.         ' Log into facebook
  16.         Case "facebook"
  17.  
  18.             'Navigate IE to site
  19.             Call NavigateTo("https://www.facebook.com/")
  20.             
  21.             
  22.             'Login with UN & PASS
  23.             Call NavigateHTML("input", "name", "email", "myUsername")
  24.             Call NavigateHTML("input", "name", "password", "myPass")
  25.             Call NavigateHTML("input", "value", "Log in", "")
  26.  
  27. 		
  28.         ' Log into example site
  29.         Case "example"
  30.  
  31.             'Navigate IE to site
  32.             Call NavigateTo("http://www.example.org/")
  33.             
  34.             
  35.             'Login with UN & PASS
  36.             Call NavigateHTML("input", "name", "USER", "myUsername")
  37.             Call NavigateHTML("Input", "name", "PASSWORD", "myPass")
  38.             Call NavigateHTML("Input", "value", "LogIn", "")
  39.             
  40.             'Accept Computer Misuse Act
  41.             Call NavigateHTML("input", "src", "/confirm.gif", "")
  42.             
  43.             'Accept terms and conditions of website use
  44.             Call NavigateHTML("input", "type", "checkbox", "accepttandc")
  45.             Call NavigateHTML("input", "value", "Submit", "")
  46.             
  47.             'Select portal hyperlink
  48.             Call NavigateHTML("a", "href", "Notice portal", "")
  49.             
  50.             'Enter pin and click gif input.
  51.             Call NavigateHTML("input", "name", "pin", "1234")
  52.             Call NavigateHTML("input", "src", "/content/skins/roll.gif", "")
  53.  
  54.     End Select
  55. 	
  56.     Do While IE.Busy = True
  57.         'Animate Progress bar DoEvents()
  58.     Loop
  59.  
  60.     LogIn = "True"
  61. End Function
  62.  
  63.  
  64.  
  65.  
  66. Public Function NavigateTo(ByVal site As String)
  67.     
  68.     With IE
  69.         .Navigate (site)
  70.         Do While .Busy = True
  71.             'Animate Progress bar
  72.         Loop
  73.     End With
  74.  
  75.     Set HTMLDoc = IE.Document
  76.     Do While HTMLDoc.ReadyState <> "complete"
  77.     Loop
  78.     
  79.     Set iHTMLCol = Nothing
  80.     While iHTMLCol Is Nothing
  81.         Set iHTMLCol = HTMLDoc.all.tags("input")
  82.     Wend
  83.     
  84. End Function




                     

  1. Function CircuitIDIs(ByVal CircuitID As String)
  2.  
  3.     Dim ValidateItRtn As String
  4.     ValidateItRtn = "NoMatch"
  5.  
  6.     Dim Regex As Object
  7.     Set Regex = CreateObject("vbscript.regexp")
  8.     Regex.IgnoreCase = True
  9.     Regex.Global = True
  10.  
  11. 	Regex.Pattern = "^([0]\d{9,10})$"    
  12.     If (Regex.test(CircuitID)) Then ValidateItRtn = "DN"
  13.     
  14.     Regex.Pattern = "^\D{4}\d{7}$"
  15.     If (Regex.test(CircuitID)) Then ValidateItRtn = "SMPFID"
  16.     
  17.     Regex.Pattern = "^(CBUK\d{6}|CBUK\d{8})$"
  18.     If (Regex.test(CircuitID)) Then ValidateItRtn = "CBUK"
  19.  
  20.  
  21.     CircuitIDIs = ValidateItRtn
  22. End Function