title: Scraping data from website using Excel VBA
body: <p>I am going to the following website:</p>
  
  <p><a href="https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=1&amp;companyName=&amp;address1=1642+Harmon+Street&amp;address2=&amp;city=Berkeley&amp;state=CA&amp;urbanCode=&amp;postalCode=&amp;zip=94703" rel="nofollow">https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=1&amp;companyName=&amp;address1=1642+Harmon+Street&amp;address2=&amp;city=Berkeley&amp;state=CA&amp;urbanCode=&amp;postalCode=&amp;zip=94703</a></p>
  
  <p>And I am trying to extract the first zip+4 that shows up (94703-2636).</p>
  
  <pre><code>Dim doc As HTMLDocument
  Set doc = IE.document
  On Error Resume Next
  output = doc.getElementsByClassName("zip4")(0).innerText
  'Sheet1.Range("E2").Value = output
  MsgBox output
  
  'IE.Quit
  End Sub
  </code></pre>
  
  <p>This is how I am trying to do it, but either the textbox or adding the data to the range gives a blank answer. That's not the full code, but everything before seems to be working alright.</p>
  
  <p>Any thoughts on how may I solve this? Thank you very much!</p>
  
  <p>EDIT: This is my full code:</p>
  
  <p>The cells it is referencing are the ones with the full address.</p>
  
  <pre><code>Sub USPS()
  
  Dim IE As Object
  Set IE = CreateObject("InternetExplorer.Application")
  IE.Visible = True
  
  IE.Navigate "https://tools.usps.com/go/ZipLookupAction!input.action?mode=1&amp;refresh=true"
  Do
  DoEvents
  Loop Until IE.READYSTATE = 4
  
  Dim Address As String
  Address = Sheet1.Range("A2").Value
  
  Dim City As String
  City = Sheet1.Range("B2").Value
  
  Dim State As String
  State = Sheet1.Range("C2").Value
  
  Dim Zipcode As String
  Zipcode = Sheet1.Range("D2").Value
  
  
  Call IE.document.getElementbyID("tAddress").SetAttribute("value", Address)
  Call IE.document.getElementbyID("tCity").SetAttribute("value", City)
  With IE.document.getElementbyID("sState")
      For i = 0 To .Length - 1
          If .Item(i).Value = State Then
              .Item(i).Selected = True
              Exit For
          End If
      Next
  
  End With
  
  Call IE.document.getElementbyID("Zzip").SetAttribute("value", Zipcode)
  
  Set ElementCol = IE.document.getElementbyID("lookupZipFindBtn")
  ElementCol.Click
  
  
  ''''' Hard Part
  
  Dim doc As HTMLDocument
  Set doc = IE.document
  On Error Resume Next
  output = Trim(doc.getElementsByClassName("zip4")(0).innerText)
  'Sheet1.Range("E2").Value = output
  MsgBox output
  
  'IE.Quit
  End Sub
  </code></pre>
  
  <p>EDIT 2: XML with Dynamic URL?</p>
  
  <pre><code>Sub ZipLookUp()
  Dim URL As String, xmlHTTP As Object, html As Object, htmlResponse As String
  Dim SStr As String, EStr As String, EndS As Integer, StartS As Integer
  Dim Zip4Digit As String
  
  Dim number As String
  Dim address As String
  Dim city As String
  Dim state As String
  Dim zipcode As String
  Dim abc As String
  
  number = Sheet1.Range("A2")
  address = Sheet1.Range("B2")
  city = Sheet1.Range("C2")
  state = Sheet1.Range("D2")
  zipcode = Sheet1.Range("E2")
  
      URL = "https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=1&amp;companyName=&amp;address1="
      URL = URL &amp; number &amp; "+" &amp; address &amp; "&amp;address2=&amp;city=" &amp; city &amp; "&amp;state=" &amp; state &amp; "&amp;urbanCode=&amp;postalCode=&amp;zip=" &amp; zipcode
      Set xmlHTTP = CreateObject("MSXML2.XMLHTTP")
      xmlHTTP.Open "GET", URL, False
      On Error GoTo NoConnect
      xmlHTTP.send
      On Error GoTo 0
      Set html = CreateObject("htmlfile")
      htmlResponse = xmlHTTP.responseText
      If htmlResponse = Null Then
          MsgBox ("Aborted - HTML response was null")
          GoTo End_Prog
      End If
  
      SStr = "&lt;span class=""zip4""&gt;": EStr = "&lt;/span&gt;&lt;br /&gt;" 'Searches for a string within 2 strings
      StartS = InStr(1, htmlResponse, SStr, vbTextCompare) + Len(SStr)
      EndS = InStr(StartS, htmlResponse, EStr, vbTextCompare)
      Zip4Digit = Left(Mid(htmlResponse, StartS, EndS - StartS), 4)
  
      Sheet1.Range("F2").Value = Zip4Digit
  
  GoTo End_Prog
  NoConnect:
      If Err = -2147467259 Or Err = -2146697211 Then MsgBox "Error - No Connection": GoTo End_Prog 'MsgBox Err &amp; ": " &amp; Error(Err)
  End_Prog:
  End Sub
  </code></pre>