Excel VBA 查询外部.aspx 页面并检索数据

Excel VBA query external .aspx page and retrieve data

提问by Raystafarian

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.

我已经为此苦苦挣扎了大约一天。基本上，我想编写一个 excel 宏来循环遍历 excel 中的列表，查询网页并检索一些数据。理想情况下，我只想检索我需要的数据，这样我就可以将它放在相邻的单元格中，但此时我会做任何事情。

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.

该页面是 ASP.net，我没有经验；如果是 .php 我可能可以管理，但我什至不确定如何通过 javascript 发布到 .aspx。

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:

我可以很好地循环我的数据，一旦我得到数据，我就可以将它写入excel，所以有两个部分我正在努力：

Part 1 – querying the webpage

第 1 部分 - 查询网页

This is the page I want to query. I need to search in Property Addressand 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.

这是我要查询的页面。我需要Property Address在结果中搜索和检索数据。我将用于示例的地址是400 W Church St. 我认为提交像“.../ParcelSearch.aspx?name=...&value=...”这样的表单可能很简单，但没有骰子。

Part 2 – grabbing the data

第 2 部分 – 抓取数据

On the results, there is a table DetailsSummary_Masterup top, with fieldsets that are defined with <legend>tags. I need the data in <legend>Municipality</legend>:

在结果上，有一个表格DetailsSummary_Master，上面有用<legend>标签定义的字段集。我需要数据<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.

我不知道该怎么做，循环遍历<td>s？我想也许我可以 GetElementByID 或通过标签，但我似乎无法弄清楚。

VBA

VBA

I used a few SO threads to try to figure it out so far. First, Secondand Third, but I can't even seem to get it to POST properly. I'm keeping the subs separate for now.

到目前为止，我使用了一些 SO 线程来尝试弄清楚。First, Second和Third，但我什至似乎无法正确发布它。我现在将潜艇分开。

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.

我实际上不需要通过 IE 运行它，我想把它全部隐藏起来。我在工作时在 Excel 2007 上运行它，但我家里有 2010。我们也有可笑的 IE8，所以越少越好。我可以循环或使用数组，但我似乎无法与查询交互。任何帮助将不胜感激。

采纳答案by J. Polfer

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.

为了进行查询，考虑到 ASPX 页面在回发时所期望的表单字段的复杂性，您可能会发现在进行此调用时更容易控制浏览器。它会相当慢，但它应该可以工作。

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

一个相当可靠的工具是Selenium，并且有一些插件可以从 Excel VBA控制Selenium。

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.

编辑：此 Excel VBA 代码片段应读出“奥兰多市”。您需要参数化以下代码并为最终版本添加错误条件的案例，以便通过任何街道地址查询以获取其市政当局。这应该让你开始。我使用带有 Firefox 的 Selenium IDE 根据记录用户操作生成 VBA 代码，然后想出了一个 XPath 查询来获取文本。

  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