Retrieving Web POST data
 

Hi. I'm reasonably proficient in excel, but most of my data importing
experience has been using text files, csv's, or occasionally html
tables.

In this particular instance, there's a page on a web site with a post
form, and i'd like to create a query for excel to look up each
parameter in the appropriate column, and deliver retrieved data into a
separate column.

Specifically, I'm trying to use this page:
http://gis.nyc.gov/vote/ps/index.htm

I can set up a column for number, street, and borough. I'd like the
Assembly district, election district, and poll site number and address
each posted into their own columns.

Someone please help if you can. Thank you.

Best,
Seth Samuels


--
Seth
------------------------------------------------------------------------
Seth's Profile: http://www.excelforum.com/member.php...o&userid=26770
View this thread: http://www.excelforum.com/showthread...hreadid=400262

Retrieving Web POST data
 

Hi Seth,

Here's a bit of code that should give you what you need. In order to run
Demo(), you'll have to set a reference to "Microsoft XML, v5.0" via Tools |
References. Basically, you could call the guGetPollingInfo function for
each row of input data, then return the appropriate values back to the
output columns as appropriate.

Public Type guPollingInfo
AssemblyDistrict As String
ElectionDistrict As String
PollSiteNumber As String
End Type

Public Function guGetPollingInfo(rsStreetNumber As String, _
rsStreetName As String, rsBorough As String) As guPollingInfo
Dim xml As XMLHTTP50
Dim sPost As String
Dim abytPostData() As Byte
Dim sResponse As String
Dim uPI As guPollingInfo
Dim lPos As Long
Dim lStart As Long
Dim lEnd As Long

sPost = "number=" & rsStreetNumber & "&"
sPost = sPost & "street=" & rsStreetName & "&"
sPost = sPost & "borough=" & rsBorough

abytPostData = StrConv(sPost, vbFromUnicode)
Set xml = New XMLHTTP50
With xml
.Open "POST", _
"http://gis.nyc.gov/vote/ps/index.htm"
.setRequestHeader "Content-Type", _
"application/x-www-form-urlencoded"
.send abytPostData
sResponse = .responseText
End With

Set xml = Nothing

'/ get poll site #
lPos = InStr(1, sResponse, "Poll Site Number:", vbTextCompare)
If lPos Then
lStart = InStr(lPos, sResponse, "</strong", vbTextCompare) + 9
If lStart Then
lEnd = InStr(lStart, sResponse, "<br", vbTextCompare) - 1
If lEnd Then
uPI.PollSiteNumber = Trim$(Mid$(sResponse, lStart, lEnd -
lStart + 1))
End If
End If
End If

'/ get assembly district
lPos = InStr(1, sResponse, "Assembly:", vbTextCompare)
If lPos Then
lStart = InStr(lPos, sResponse, "</strong", vbTextCompare) + 9
If lStart Then
lEnd = InStr(lStart, sResponse, "<br", vbTextCompare) - 1
If lEnd Then
uPI.AssemblyDistrict = Trim$(Mid$(sResponse, lStart, lEnd -
lStart + 1))
End If
End If
End If

'/ get election district
lPos = InStr(1, sResponse, "Election:", vbTextCompare)
If lPos Then
lStart = InStr(lPos, sResponse, "</strong", vbTextCompare) + 9
If lStart Then
lEnd = InStr(lStart, sResponse, "<br", vbTextCompare) - 1
If lEnd Then
uPI.ElectionDistrict = Trim$(Mid$(sResponse, lStart, lEnd -
lStart + 1))
End If
End If
End If

guGetPollingInfo = uPI
End Function

Public Sub demo()
Dim uPI As guPollingInfo

uPI = guGetPollingInfo("123", "1st ave", "Manhattan")

Debug.Print "Assembly: " & uPI.AssemblyDistrict
Debug.Print "Election: " & uPI.ElectionDistrict
Debug.Print "Poll Site: " & uPI.PollSiteNumber
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Seth wrote:
Hi. I'm reasonably proficient in excel, but most of my data importing
experience has been using text files, csv's, or occasionally html
tables.

In this particular instance, there's a page on a web site with a post
form, and i'd like to create a query for excel to look up each
parameter in the appropriate column, and deliver retrieved data into a
separate column.

Specifically, I'm trying to use this page:
http://gis.nyc.gov/vote/ps/index.htm

I can set up a column for number, street, and borough. I'd like the
Assembly district, election district, and poll site number and address
each posted into their own columns.

Someone please help if you can. Thank you.

Best,
Seth Samuels

Retrieving Web POST data
 

I'm still having trouble making this work. I went into Visual Basi
(something I'm not sufficiently familiar with, I'm ashamed to admit)
and pasted your code in, but could not subsequently run
guPollingInfo() or Demo() function in my worksheet without getting
#NAME? error.

Edits that I tried (mostly guessing) that didn't work we

-changing guGetPollingInfo to guPollingInfo to create uniformity
-changing As String to As Variant in the code for the three inpu
variables.



should I be using the three '/ get commands independently of on
another in different functions? Is this something as silly as I need t
save it somewhere and don't know how?

Also, the most important thing for me to get is the address, and
don't see any code for that, though I might be missing it. Though
realize that was sort of obscured in my original request.

Actually what I would love to be able to do is input the poll sit
number and get the address. If I could do that, the rest wouldn'
matter. I just assume that because of how the form is set up, I need t
input the number, street, and borough variables.

My problem is that while I know excel and have a vague understanding o
code, I don't really know how to implement this.

If anyone has the time to talk me through setting this up, I'd greatl
appreciate it. I'm available on AIM at Sethuels all day.

Thanks.

-Set

--
Set
-----------------------------------------------------------------------
Seth's Profile: http://www.excelforum.com/member.php...fo&userid=2677
View this thread: http://www.excelforum.com/showthread.php?threadid=40026