Excel VBA requests an external .aspx page and retrieves data

Question

I struggled with this for about a day. Basically, I want to write an excel macro to scroll through a list in excel, request a webpage and get some data. Ideally, I just want to get the data I need, so I can put it in the next cell, but I would do everything that is needed at this moment.

ASP.net page with which I have no experience; if it were .php, I could probably manage it, but I'm not even sure how to send messages to .aspx via javascript.

I can accurately draw my data, and as soon as I get the data, I can write it to succeed, so there are two parts that I encounter:

Part 1 - requesting a web page

This is the page I want to request . I need to search in the Property Address and retrieve data from the results. The address I will use as an example is 400 W Church St I thought it might just be submitting a form like "... / ParcelSearch.aspx? Name = ... & value = ..." but not a cube.

Part 2 - Data Capture

As a result, there is a DetailsSummary_Master table at the top, with fields that are defined with <legend> tags. I need data in <legend>Municipality</legend> :

I can’t figure out what to do, iterate over <td> s? I thought maybe I could getElementByID or maybe with a tag, but I can’t figure out how to do this.

Vba

I have used several SO threads to try to figure this out so far. The first , second and third , but I can’t even seem that it works correctly with POST. I keep the hooks separate.

This is what I (stole from another thread) regarding my problem:

 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 = "?name=ctl00_ctl00_ctl00_ctl00_ContentMain_ContentMain_ContentMain_ContentMain_TabContainer1_Searches_SubTabContainer1_QuickSearches_CompositAddressSearch1_AddressSearch1_ctl00_Address&value=" strValue = "400 W Church St" strSubmit = strID & strValue strUrl = "http://www.ocpafl.org/searches/ParcelSearch.aspx" Set objIE = CreateObject("InternetExplorer.Application") objIE.navigate "about:blank" Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP") '~~> Indicates that page that will receive the request and the type of request being submitted xmlhttp.Open "POST", "http://www.ocpafl.org/searches/ParcelSearch.aspx", False '~~> Indicate that the body of the request contains form data xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" '~~> Send the data as name/value pairs xmlhttp.Send "strSubmit" strResponse = xmlhttp.responseText objIE.navigate strUrl objIE.Visible = True Do While objIE.readystate <> 4 DoEvents Loop objIE.document.Write strResponse Set xmlhttp = Nothing End Sub 

I really don't need to run it through IE, I would like to hide it all. I am working on this in Excel 2007 at work, but I have 2010 at home. We also have funny IE8, so the less the better. And I can use a loop or use an array, but I just can't interact with the request. Any help would be greatly appreciated.

Answer 1

To make a request, given the complexity of the form fields that an ASPX page expects upon postback, it might be easier for you to control the browser when making this call. It will be rather slow, but it should work.

A fairly reliable tool for this Selenium , and there are plugins for managing Selenium from Excel VBA .

Edit: This Excel VBA code snippet should read Orlando City Hall. You need to parameterize the code below and add cases for the error conditions for your final version to query at any address in order to get its municipality. This should help you get started. I used the Selenium IDE with Firefox to generate VBA code based on a record of user actions, and then an XPath request came up to capture the text.

  Dim selenium As New SeleniumWrapper.WebDriver selenium.Start "firefox", "http://www.ocpafl.org/searches/ParcelSearch.aspx" selenium.setImplicitWait 5000 selenium.setImplicitWait 5000 selenium.Open "/searches/ParcelSearch.aspx" selenium.Click "id=popup_ok" selenium.Type "id=ctl00_ctl00_ctl00_ctl00_ContentMain_ContentMain_ContentMain_ContentMain_TabContainer1_Searches_SubTabContainer1_QuickSearches_CompositAddressSearch1_AddressSearch1_ctl00_Address", "400 W Church St" selenium.Click "id=ctl00_ctl00_ctl00_ctl00_ContentMain_ContentMain_ContentMain_ContentMain_TabContainer1_Searches_SubTabContainer1_QuickSearches_PropertyNameSearch1_ctl00" selenium.Click "id=ctl00_ctl00_ctl00_ctl00_ContentMain_ContentMain_ContentMain_ContentMain_TabContainer1_Searches_SubTabContainer1_QuickSearches_CompositAddressSearch1_AddressSearch1_ctl00_ActionButton1" Dim municipalityResult As String municipalityResult = selenium.getText("//fieldset[contains(legend,'Municipality')]") selenium.stop