在 VBA 中通过 XMLHTTP 发送表单数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6984528/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sending Form Data Through XMLHTTP in VBA
提问by D. Brown
I'm trying to send form data through the XMLHTTP object to get a webpage.
我正在尝试通过 XMLHTTP 对象发送表单数据以获取网页。
I am using Excel 2010.
我正在使用 Excel 2010。
The website is http://espn.go.com/mlb/players.
该网站是http://espn.go.com/mlb/players。
I'm trying to search for a certain player through the searchbox (e.g. Fister).
我正在尝试通过搜索框（例如 Fister）搜索某个玩家。
Here is the source code between the form tags.
这是表单标签之间的源代码。
<form id="searchBox" name="searchBox" action="http://search.espn.go.com/results" method="get" accept-charset="utf-8" style="color: #999999;">
<div class="clearfix">
<input autocomplete="off" class="text" type="text" placeholder="Search" name="searchString" id="searchString" />
<input type="hidden" name="page" id="page" value="null" />
<input type="hidden" name="fromForm" value="true" />
<input class="submit" type="submit" value="" />
</div>
</form>
My code.
我的代码。
Sub SearchPlayer()
Dim xml As MSXML2.ServerXMLHTTP
Dim search, url As String
search = "searchString=Fister&page=null&fromForm=true"
url = "http://espn.go.com/mlb/players"
Set xml = New MSXML2.ServerXMLHTTP
xml.Open "POST", url, False
xml.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
xml.send search
MsgBox xml.responseText
Set xml = Nothing
End Sub
回答by JimmyPena
This code worked for me:
这段代码对我有用：
Function SearchPlayer(playerName As String) As String
Dim xml As MSXML2.XMLHTTP60
Dim result As String
Const BASE_URL As String = "http://search.espn.go.com/results?searchString={name}&page=null&fromForm=true"
Set xml = CreateObject("MSXML2.XMLHTTP.6.0")
With xml
.Open "GET", Replace(BASE_URL, "{name}", playerName), False
.send
End With
result = xml.responseText
SearchPlayer = result
End Function
(assumes you have MSXML 6.0 on your system -- msxml6.dll in your local system32 folder)
（假设您的系统上有 MSXML 6.0——本地 system32 文件夹中的 msxml6.dll）
As stated, the form uses a GET request so you would use the ACTION attribute and append the INPUT tags' values onto a single string like this:
如上所述，该表单使用 GET 请求，因此您可以使用 ACTION 属性并将 INPUT 标签的值附加到单个字符串中，如下所示：
http://search.espn.go.com/results?searchString=Fister&page=null&fromForm=true
http://search.espn.go.com/results?searchString=Fister&page=null&fromForm=true
I functionized the Sub so you can call it with different player names to scrape each page. Of course, you would need a urlencode function if you expect it to be called with player names that have spaces in them (here's one).
我对 Sub 进行了功能化，因此您可以使用不同的玩家名称调用它来抓取每个页面。当然，如果您希望使用其中包含空格的玩家名称调用 urlencode 函数，则您需要一个 urlencode 函数（这是一个）。
回答by dee
If I am not missing something, then the URLwhere it goes after click on Searchbutton looks like this: http://www.espn.com/mlb/players?search=Fister
如果我没有遗漏一些东西，那么URL点击Search按钮后它的去向如下所示：http: //www.espn.com/mlb/players?search=Fister
It is GETrequest and it returns HTMLwhich can be then searched e.g. using standard searching functions of MSHTMLDocument, example:
它是GET请求并返回HTML，然后可以使用标准搜索功能进行搜索MSHTMLDocument，例如：
Sub SearchPlayer()
Dim http As MSXML2.ServerXMLHTTP
Dim html As MSHTML.HTMLDocument ' Add reference to Microsoft HTML Object Library
Dim url As String
Dim player As String
player = "Fister"
url = "http://www.espn.com/mlb/players?search=" & player
Set http = New MSXML2.ServerXMLHTTP
http.Open "GET", url, False
http.send
Set html = New HTMLDocument
html.body.innerHTML = http.responseText
Dim nextGamePlace As MSHTML.HTMLDivElement
Set nextGamePlace = html.querySelector("div[class='game-details'] div[class='venue']")
Debug.Print nextGamePlace.textContent ' prints Miller Park
End Sub
Note: to watch the requests, just hit F12 in your browser and select Network traffic etc.
注意：要查看请求，只需在浏览器中按 F12 并选择网络流量等。
