[AccessD] Get data From Web

Mark A Matte wrote:

> Hello All,
>
> Thanks for the feedback...but is there a way to extract data from a 
> web page using access?  Example: For example...the page always has a 
> text box labelled 'COLOR'...can I use access to open that page and 
> tell me what value is in the COLOR text box?
>
> Thanks,

 There are about a half a dozen ways to do this, some are OS dependant 
ie. they don't exist in older OS or require IE installed.
Winhttp, xmlhttprequest, InternetOpenUrl from wininet, Inet transfer 
control for VB6, Some might have to be used, to say specifically to get 
around Windows user authentication difficulties or clearing your IE 
cache if web page changes or is dynamic.

You will have to parse the big HTML returned string for the value you want.

Here is a Winhttp method , xmlhttp method is almost the same

Private Sub Command2_Click()
'set reference microsoft winhttp services 5.1
' This subroutine requests a resource and supplies authentication
' credentials.
Dim WinHttpReq As WinHttp.WinHttpRequest
Set WinHttpReq = New WinHttpRequest
'On Error GoTo Err_Command2_Click
'Assemble an HTTP request.
MsgBox "Secure Request"
    WinHttpReq.Open "GET", _
 "http://msdn.microsoft.com/downloads/samples/internet/winhttp/auth/authenticate.asp", 
False
       
    ' Set the user name and password. if required
    WinHttpReq.SetCredentials "UserName", "Password*", _
        HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
       
    ' Send the HTTP Request.
    WinHttpReq.Send
   
    ' Display the status code and response headers.
    Text2.Value = WinHttpReq.Status & " " & WinHttpReq.StatusText
    Text0.Value = WinHttpReq.GetAllResponseHeaders & "  " & 
WinHttpReq.ResponseText


 

Exit_Command2_Click:
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
   
End Sub

Here is a WinInet method  which will get you around most site oddities
There are many options available here so read the MS SDK on this

Public Const INTERNET_OPEN_TYPE_PRECONFIG = 0
Public Const INTERNET_OPEN_TYPE_DIRECT = 1
Public Const INTERNET_OPEN_TYPE_PROXY = 3
Public Const INTERNET_FLAG_RELOAD = &H80000000

Public Declare Function InternetOpen Lib "wininet.dll" _
    Alias "InternetOpenA" (ByVal sAgent As String, _
    ByVal lAccessType As Long, ByVal sProxyName As String, _
    ByVal sProxyBypass As String, ByVal lFlags As Long) As Long

Public Declare Function InternetOpenUrl Lib "wininet.dll" _
    Alias "InternetOpenUrlA" (ByVal hOpen As Long, _
    ByVal sUrl As String, ByVal sHeaders As String, _
    ByVal lLength As Long, ByVal lFlags As Long, _
    ByVal lContext As Long) As Long

Public Declare Function InternetReadFile Lib "wininet.dll" _
    (ByVal hFile As Long, ByVal sBuffer As String, _
    ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) _
    As Integer

Public Declare Function InternetCloseHandle Lib "wininet.dll" _
    (ByVal hInet As Long) As Integer

'obtain the entire text of the specified URL :

 Function GetHTMLFromURL(sUrl As String) As String

Dim s As String
Dim hOpen As Long
Dim hOpenUrl As Long
Dim bDoLoop As Boolean
Dim bRet As Boolean
Dim sReadBuffer As String * 2048
Dim lNumberOfBytesRead As Long

hOpen = InternetOpen(scUserAgent, INTERNET_OPEN_TYPE_PRECONFIG, _
    vbNullString, vbNullString, 0)
   ' If hOpen = Null Then Debug.Print "bad open url"
   ' MsgBox "Error from Internetopen: " & Err.LastDllError
hOpenUrl = InternetOpenUrl(hOpen, sUrl, vbNullString, 0, _
    INTERNET_FLAG_RELOAD, 0)
 'If hOpen = Null Then Debug.Print "bad open url 2"
   ' MsgBox "Error from InternetOpenUrl: " & Err.LastDllError
bDoLoop = True
Do While bDoLoop
    sReadBuffer = vbNullString
    bRet = InternetReadFile(hOpenUrl, sReadBuffer, Len(sReadBuffer), _
           lNumberOfBytesRead)
    s = s & Left$(sReadBuffer, lNumberOfBytesRead)
    Debug.Print "Read=" & lNumberOfBytesRead
    If Not CBool(lNumberOfBytesRead) Then bDoLoop = False
Loop

If hOpenUrl <> 0 Then InternetCloseHandle (hOpenUrl)
If hOpen <> 0 Then InternetCloseHandle (hOpen)
Debug.Print lNumberOfBytesRead
GetHTMLFromURL = s
End Function

-- 
Marty Connelly
Victoria, B.C.
Canada