Excel的VBA查询外部.aspx页和检索数据 [英] Excel VBA query external .aspx page and retrieve data
问题描述
我一直在挣扎与此有关的日子。基本上，我想通过在Excel列表来写一个Excel宏循环，查询网页和检索一些数据。理想我只想找回我需要这样我就可以把它放在相邻单元格中的数据，但我会尽一切可能在这一点上。
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.
该网页是ASP.net，我有没有经验;如果是的.php我大概可以管理，但我什至不知道如何发布通过JavaScript为.aspx。
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:
这是我想查询页面。我需要物业地址搜索和检索结果的数据。我将使用该示例的地址是 400瓦教堂圣。我想这可能是简单的提交表单，如... / ParcelSearch.aspx名= ...＆放大器;值= ...，但没有骰子
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 is 400 W Church St. I thought it may be simple to submit a form like ".../ParcelSearch.aspx?name=...&value=...", but no dice.
在结果，有一个表 DetailsSummary_Master 向上顶，与使用中所定义字段集;传奇＆GT; 标记。我需要在数据＆LT;传奇＆GT;＆本市LT; /传说＆GT; ：
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>:
我想不出该怎么做，通过，循环＆LT; TD＆GT; S'我想也许我可以的getElementById或者按标签，但我似乎无法找到答案。
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.
我使用了一些SO线程试图弄明白为止。 <一href=\"http://stackoverflow.com/questions/13364172/how-to-automate-and-export-html-searchable-query-data-into-excel\">First, <一href=\"http://stackoverflow.com/questions/209301/how-to-post-a-form-from-html-to-aspx-page\">Second和<一个href=\"http://stackoverflow.com/questions/8816557/vba-access-remote-website-automate-clicking-submit-after-already-automating-cli\">Third,但我甚至不能似乎得到它的正常发布。我保持独立潜艇现在。
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
我实际上并不需要通过IE浏览器来运行它，我想运行它隐藏起来。我在工作中的Excel 2007中运行，但我有2010家。我们也有可笑的IE8，所以越少，越好。我可以循环或使用数组，但我似乎无法与查询的接口。任何帮助将大大AP preciated。
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.
推荐答案
有关决策查询，考虑到该ASPX页面上回发预期表单字段的复杂性，你做这个的时候可能会发现更容易控制浏览器呼叫。这将是相当缓慢的，但它应该工作
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.
这个一个相当可靠的工具是硒，并有插件来控制的从Excel VBA 硒
A fairly reliable tool for this is Selenium, and there are plugins to control Selenium from Excel VBA.
编辑：这个Excel VBA code段应读出奥兰多市。你需要的参数低于code和错误条件的情况下添加您的最终版本的任何街道地址进行查询得到的直辖市。这应该让你开始虽然。我用Selenium IDE的使用Firefox根据记录用户的操作生成VBA code，然后想出了一个XPath查询抓取的文本。
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
这篇关于Excel的VBA查询外部.aspx页和检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
