Reputation: 1
I'm trying to send form data through the XMLHTTP object to get a webpage.
I am using Excel 2010.
The website is http://espn.go.com/mlb/players.
I'm trying to search for a certain player through the searchbox (e.g. 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
Upvotes: 0
Views: 24781
Reputation: 14053
If I am not missing something, then the URL
where it goes after click on Search
button looks like this: http://www.espn.com/mlb/players?search=Fister
It is GET
request and it returns HTML
which can be then searched e.g. using standard searching functions of MSHTMLDocument
, example:
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.
Upvotes: 0
Reputation: 8764
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)
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:
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).
Upvotes: 0