Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
retrieving data | Excel Worksheet Functions | |||
Retrieving SQL data into Excel | Setting up and Configuration of Excel | |||
Retrieving data from the web - help ! | Excel Worksheet Functions | |||
Retrieving Web Data | Excel Programming | |||
Retrieving data | Excel Programming |