Excel VBA查询外部.aspx页面并检索数据 [英] Excel VBA query external .aspx page and retrieve data

问题描述

我一直在努力工作大概一天。基本上，我想编写一个excel宏循环遍历excel中的列表，查询网页并检索一些数据。理想情况下，我只是要检索所需的数据，所以我可以把它放在一个相邻的单元格中，但是在这一点上我会做任何事情。

页面是ASP.net，我没有经验;如果是.php我可以管理，但我甚至不知道如何通过javascript发布到.aspx。

我可以循环访问我的数据，一旦我得到数据，我可以写它到excel，所以有两个部分我正在努力：

第1部分 - 查询网页

，第二个和

这是我的问题（从另一个线程偷来的）我的问题：

  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
设置objIE = CreateObject（InternetExplorer.Application）
 objIE.navigateabout：blank
设置xmlhttp = CreateObject（MSXML2.ServerXMLHTTP）
 
'~~>表示将收到请求的页面和正在提交的请求类型
 xmlhttp.OpenPOST，http://www.ocpafl.org/searches/ParcelSearch.aspx，False 
 
'~~>表示请求的正文包含表单数据
 xmlhttp.setRequestHeaderContent-Type，application / x-www-form-urlencoded
 
'~~>发送数据作为名称/值对
 xmlhttp.SendstrSubmit
 strResponse = xmlhttp.responseText 
 objIE.navigate strUrl 
 objIE.Visible = True 
 
 Do While objIE.readystate<> 4 
 DoEvents 
循环
 
 objIE.document.Write strResponse 
 
设置xmlhttp =没有
 
 End Sub $ b $我实际上不需要通过IE运行它，我想运行它全部隐藏。我在Excel 2007上运行这个工作，但我在2010年。我们也有可笑的IE8，所以越少越好。而且我可以循环或使用数组，但是我似乎无法与查询进行接口。任何帮助将不胜感激。
解决方案
为了进行查询，考虑到ASPX页面期待的表单域的复杂性在回发时，您可能会发现在进行此呼叫时控制浏览器更为容易。这将是相当缓慢，但它应该工作。
 
 
 
一个相当可靠的工具是 Selenium ，还有一些插件来控制来自Excel VBA的Selenium / a>。
 
 
 
 编辑：这个Excel VBA代码片段应该读出市政奥兰多。您需要参数化以下代码，并添加您的最终版本的错误条件的案例，以查询任何街道地址以获得其自治市。这应该让你开始。我使用Selenium IDE与Firefox来生成基于记录用户操作的VBA代码，然后找出一个XPath查询来获取文本。
  Dim selenium As New SeleniumWrapper.WebDriver 
 selenium.Startfirefox，http://www.ocpafl.org/searches/ParcelSearch.aspx
 selenium.setImplicitWait 5000 
 
 selenium.setImplicitWait 5000 
 selenium.Open/searches/ParcelSearch.aspx
 
 selenium.Clickid = popup_ok
 selenium.Typeid = ctl00_ctl00_ctl00_ctl00_ContentMain_ContentMain_ContentMain_ContentMain_TabContainer1_Searches_SubTabContainer1_QuickSearches_CompositAddressSearch1_AddressSearch1_ctl00_Address  400瓦教会圣 
 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 
  
 
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 is 400 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.
解决方案
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


                        
这篇关于Excel VBA查询外部.aspx页面并检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！