D. Brown
D. Brown

Reputation: 1

Sending Form Data Through XMLHTTP in VBA

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

Answers (2)

gembird
gembird

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

enter image description here

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

JimmyPena
JimmyPena

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

Related Questions