Excel VBA query external .aspx page and retrieve data
For making the query, given the complexity of the form fields that the ASPX page is expecting on postback, you might find it easier to control the browser when making this call. It will be rather slow, but it should work.
A fairly reliable tool for this is Selenium, and there are plugins to control Selenium from Excel VBA.
Edit: This Excel VBA code snippet should read out "Municipality Orlando". You need to parameterize the below code and add cases for error conditions for your final version to query by any street address to get its municipality. This should get you started though. I used Selenium IDE with Firefox to generate the VBA code based on recording user actions, and then came up with an XPath query to grab 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
Raystafarian
All content posted prior to September 5, 2019 was shared under CC BY-SA 3.0 US and cannot be re-licensed without my explicit permission, which I have not given. To contact me, please do via my website. Please be explicit in who you are and from where you found me. I'm open to new projects and challenges. If you're looking to hire me for a project matching my skills on this site, feel free to do so; I can be contacted via email at Ray {@} Raystafarian [com]. Be advised questions regarding clarification on posted questions/answers will be redirected back here - it's much better for knowledge to shared publicly rather than privately. 3rd party recruiters are not to contact me
Updated on June 17, 2022Comments
-
Raystafarian almost 2 years
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.
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.
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:
Part 1 – querying the webpage
This is the page I want to query. I need to search in
Property Address
and retrieve data from the results. The address I'll use for the example is400 W Church St
. I thought it may be simple to submit a form like ".../ParcelSearch.aspx?name=...&value=...", but no dice.Part 2 – grabbing the data
On the results, there is a table
DetailsSummary_Master
up top, with fieldsets that are defined with<legend>
tags. I need the data in<legend>Municipality</legend>
:I can’t figure out what to do, loop through the
<td>
s? I thought maybe I could GetElementByID or maybe by tag, but I can’t seem to figure it out.VBA
I used a few SO threads to try to figure it out so far. First, Second and Third, but I can't even seem to get it to POST properly. I'm keeping the subs separate for now.
This is what I have (stolen from the other thread) in regards to 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 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.
-
Raystafarian over 10 yearsThanks, I will check this out. I don't have install rights at work, but I might be able to figure out a way.
-
Raystafarian over 10 yearsI'm going to use your answer and request access to these tools. I think I'll
accept
it as well, even though it doesn't answer the question in the way that I wanted, it still answers my particular question. Thanks!