title: Excel VBA query external .aspx page and retrieve data
body: <p>I've been struggling with this for about a day. Basically, I want to write an excel macro to loop through a list in excel, query a webpage and retrieve some data. Ideally I just want to retrieve the data I need so I can place it in an adjacent cell, but I'd do whatever it takes at this point.</p>
  <p>The page is ASP.net, which I have no experience in; if it was .php I could probably manage, but I’m not even sure how to post to .aspx through javascript.</p>
  <p>I can loop through my data just fine, and once I get the data I can write it to excel, so there are two parts I’m struggling with:</p>
  <h3>Part 1 – querying the webpage</h3>
  <p><a href="http://www.ocpafl.org/searches/ParcelSearch.aspx" rel="noreferrer">This is the page I want to query</a>. I need to search in <code>Property Address</code> and retrieve data from the results. The address I'll use for the example is <code>400 W Church St</code>. I thought it may be simple to submit a form like &quot;.../ParcelSearch.aspx?name=...&amp;value=...&quot;, but no dice.</p>
  <h3>Part 2 – grabbing the data</h3>
  <p>On the results, there is a table <code>DetailsSummary_Master</code> up top, with fieldsets that are defined with <code>&lt;legend&gt;</code> tags. I need the data in <code>&lt;legend&gt;Municipality&lt;/legend&gt;</code>:
  <img src="https://i.stack.imgur.com/bXKP0.png" alt="enter image description here" /></p>
  <p>I can’t figure out what to do, loop through the <code>&lt;td&gt;</code>s? I thought maybe I could GetElementByID or maybe by tag, but I can’t seem to figure it out.</p>
  <h3>VBA</h3>
  <p>I used a few SO threads to try to figure it out so far. <a href="https://stackoverflow.com/questions/13364172/how-to-automate-and-export-html-searchable-query-data-into-excel">First</a>, <a href="https://stackoverflow.com/questions/209301/how-to-post-a-form-from-html-to-aspx-page">Second</a> and <a href="https://stackoverflow.com/questions/8816557/vba-access-remote-website-automate-clicking-submit-after-already-automating-cli">Third</a>, but I can't even seem to get it to POST properly. I'm keeping the subs separate for now.</p>
  <p>This is what I have (stolen from the other thread) in regards to my problem:</p>
  <pre><code>Sub SubmitForm()
  
  Dim objIE As Object
  Dim xmlhttp As Object
  Dim ieButton As Object
  Dim strResponse As String
  Dim strUrl As String
  Dim strID As String
  Dim strValue As String
  Dim strSubmit As String
  
  strID = &quot;?name=ctl00_ctl00_ctl00_ctl00_ContentMain_ContentMain_ContentMain_ContentMain_TabContainer1_Searches_SubTabContainer1_QuickSearches_CompositAddressSearch1_AddressSearch1_ctl00_Address&amp;value=&quot;
  strValue = &quot;400 W Church St&quot;
  strSubmit = strID &amp; strValue
  
  
  strUrl = &quot;http://www.ocpafl.org/searches/ParcelSearch.aspx&quot;
  Set objIE = CreateObject(&quot;InternetExplorer.Application&quot;)
  objIE.navigate &quot;about:blank&quot;
  Set xmlhttp = CreateObject(&quot;MSXML2.ServerXMLHTTP&quot;)
  
  '~~&gt; Indicates that page that will receive the request and the type of request being submitted
  xmlhttp.Open &quot;POST&quot;, &quot;http://www.ocpafl.org/searches/ParcelSearch.aspx&quot;, False
  
  '~~&gt; Indicate that the body of the request contains form data
  xmlhttp.setRequestHeader &quot;Content-Type&quot;, &quot;application/x-www-form-urlencoded&quot;
  
  '~~&gt; Send the data as name/value pairs
  xmlhttp.Send &quot;strSubmit&quot;
  strResponse = xmlhttp.responseText
  objIE.navigate strUrl
  objIE.Visible = True
  
  Do While objIE.readystate &lt;&gt; 4
      DoEvents
  Loop
  
  objIE.document.Write strResponse
  
  Set xmlhttp = Nothing
  
  End Sub
  </code></pre>
  <p>I don't actually need to run it through IE, I'd like to run it all hidden. I'm running this on Excel 2007 at work, but I have 2010 at home. We also have ridiculous IE8, so the less of that, the better. And I can loop or use an array, but I just can't seem to interface with the query. Any help would be greatly appreciated.</p>