Paul Rodgers
Paul.Rogers at SummitMedia.co.uk
Wed Jun 15 03:11:02 CDT 2005
That's amazing, Marty. Thanks very much for the guidance and for the code
etc. Much obliged to you.
Cheers paul
-----Original Message-----
From: MartyConnelly [mailto:martyconnelly at shaw.ca]
Sent: 14 June 2005 18:01
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Web page to Access
It can be done by screen scraping via xmlhttp request object, winhhtp or
wininet.
Essentially you send a http post command like
www.somesite.com?table=A&recordrange=100
It then returns a text string which is a copy of the html screen
You then parse the returned html for the values desired.
Really easy if html is xhtml compliant (ha ha rarely true) you can then
use xslt to parse out values
Drawbacks if html page format changes (common) you may have rewrite
parser code.
also some sites may do page redirects for security to stop this type of
scraping or have hidden the asp source.
Some of the bigger or more technically adept companies may provide this
data as a web service.
Needs some knowledge of html forms to get it to work.You may have to
figure out button code
on original html source of the request page.
If you don't have a formal data sharing agreement with the company, I
wouldn't bet the farm on it.
for a whole variety of reasons.
I know one VP of an airline in Canada went to jail or was severely
fined for screenscraping a competitors route and load info
from an internal website. He ummm borrowed a password from an
ex-employee of the competitor and then hit the webpage thousands
of time via code using this method, they sort of noticed the spike of
hits in the middle of the night.
Here is some sample code that works off a webservice for limited US
Canada address testing.
See here for info.
http://ws.cdyne.com/psaddress/addresslookup.asmx/
You need a purchased license key other than 0 for a large volume.
To use this code elsewhere
You would just change oXMLHTTP.send string to whatever parameters comes
behind "?"
and then read or dump returned string to a file with the returned html
text in oXMLHTTP.responseText
and then parse text string for your values.
This site returns xml rather than straight text so easier to parse with
DOM or XSLT or XPath query.
Sub test()
Dim strAddress As String
strAddress = AddrCorrect("One Microsoft Way", "Redmond", "WA",
"98052-6399", "0")
MsgBox strAddress
strAddress = AddrCorrect("1 Microsoft Way", "Redmond", "WA",
"98052-6399", "0")
MsgBox strAddress
strAddress = AddrCorrect("One Lake St", "Upper Saddle River", "NJ",
"07458", "0")
MsgBox strAddress
strAddress = AddrCorrect("", "Redmond", "WA", "", "0")
MsgBox strAddress
strAddress = AddrCorrect("417 Fifth Ave", "New York", "NY", "10016", "0")
'more than 1 zip code
MsgBox strAddress
strAddress = AddrCorrect("2400 East Bayshore Road", "Palo Alto", "CA",
"94303", "0")
MsgBox strAddress
Debug.Print strAddress
End Sub
Public Function AddrCorrect(ByRef address As String, ByRef city As
String, ByRef state As String, ByRef zip As String, Optional LicenseKey
As String) As String
'Dim oXMLHTTP As MSXML2.ServerXMLHTTP
Dim oXMLHTTP As Object
' Call the web service to get an XML document
Set oXMLHTTP = CreateObject("Msxml2.ServerXMLHTTP")
' Set oXMLHTTP = New MSXML2.ServerXMLHTTP
oXMLHTTP.Open "POST", _
"http://ws.cdyne.com/psaddress/addresslookup.asmx/CheckAddress", _
False
oXMLHTTP.setRequestHeader "Content-Type", _
"application/x-www-form-urlencoded"
oXMLHTTP.send "AddressLine=" & URLEncode(address) & "&ZipCode=" &
URLEncode(zip) & "&City=" & URLEncode(city) & "&StateAbbrev=" &
URLEncode(state) & "&LicenseKey=" & URLEncode(LicenseKey)
If oXMLHTTP.status <> 200 Then
MsgBox "Service Unavailable. Try again later"
Set oXMLHTTP = Nothing
Exit Function
End If
'Dim oDOM As Object
' doesn't work why Dim oDOM As MSXML2.DOMDocument50
Dim oDOM As MSXML2.DOMDocument40
Set oDOM = oXMLHTTP.responseXML
'Debug.Print oDOM.xml
Dim oNL As Object
Dim oCN As Object
Dim oCC As Object
Set oNL = oDOM.getElementsByTagName("Address")
For Each oCN In oNL
For Each oCC In oCN.childNodes
Select Case LCase(oCC.nodeName)
Case "serviceerror"
If CBool(oCC.Text) = True Then
AddrCorrect = "Service Error. Try again Later"
GoTo leaveit
End If
Case "addresserror"
If CBool(oCC.Text) = True Then
AddrCorrect = "Address uncorrectable."
Debug.Print oDOM.xml
GoTo leaveit
End If
Case "servicecurrentlyunavailable"
If CBool(oCC.Text) = True Then
AddrCorrect = "Service Unavailable. Try again Later"
GoTo leaveit
End If
Case "addressfoundbemorespecific"
If CBool(oCC.Text) = True Then
AddrCorrect = "Address Found. Be more Specific."
GoTo leaveit
End If
Case "deliveryaddress"
address = oCC.Text
Case "city"
city = oCC.Text
Case "stateabbrev"
state = oCC.Text
Case "zipcode"
zip = oCC.Text
End Select
Next
Next
AddrCorrect = "OK" ' Address corrected
leaveit:
Set oCC = Nothing
Set oCN = Nothing
Set oNL = Nothing
Set oDOM = Nothing
Set oXMLHTTP = Nothing
End Function
Public Function URLEncode(inS As String) As String
Dim i As Long
Dim inC, outC As String
For i = 1 To Len(inS)
inC = Mid(inS, i, 1)
Select Case inC
Case " "
outC = "+"
Case "&"
outC = "%38"
Case "!" To "~"
outC = inC
Case Else
outC = "%" + Right("00" + Hex(Asc(inC)), 2)
End Select
URLEncode = URLEncode + outC
Next i
End Function
Paul Rodgers wrote:
>The client asks: Can information in a table (Name, Address, City, etc) on a
>webpage be fed (coded) automatically straight into Access 2k?
>
>I said, 'I don't think so.' But perhaps it is possible. Anyone any
>experience, please?
>
>All the best
>paul
>
>
>
--
Marty Connelly
Victoria, B.C.
Canada
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.1 - Release Date: 13/06/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.3/15 - Release Date: 14/06/2005