Access to Import from Web

[ryguy7272]

I saw the code below on a Forms Programming DG a while back:

'=======/START CODE/========
Dim strUrl As String
Dim strHTML As String
' Initialize the URL to search
strUrl = "finance.yahoo.com"
' Use our GetFromWebpage function to
' retrieve the HTML from the site
strHTML = GetFromWebpage(strUrl)

txtInput.SetFocus
txtInput.Text = ""
txtInput.Value = strHTML
'--FUNCTION---

Function GetFromWebpage(URL As String) As String
Dim objWeb As Object
Dim strXML As String
' Instantiate an instance of the web object
Set objWeb = CreateObject("Microsoft.XMLHTTP")
' Pass the URL to the web object, and send the request
objWeb.Open "GET", URL, False
objWeb.Send
' Look at the HTML string returned
strXML = objWeb.responseText
GetFromWebpage = strXML
End Function
'======/END CODE/=========

When I try to run the code I get a message that says ‘Compile Error: Invalid
Outside Procedure’. This line is highlighted:

strUrl = http://finance.yahoo.com/



I’m not sure how the code works. Can someone please explain it to me? For
instance, does this sit behind the Form? How is it initiated? Do I call it
from a TextBox? From a Button on a From? OnLoad?



Also the poster was talking about refreshing the procedure with new HTML data:

====/START CODE/====
Private Declare Function DeleteUrlCacheEntry Lib "wininet.dll" Alias
"DeleteUrlCacheEntryA" (ByVal lpszUrlName As String) As Long
form_load()
DeleteUrlCacheEntry "finance.yahoo.com"
end sub
====/END CODE/====

How does that work? Is that called by the function, or is it somehow
embedded in the function? I am trying to learn how to import data from a web
site, like finance.yahoo.com, into an Access table.



Ideally, I’d like to be able to import data for four stocks, with security
symbols listed in an Access table, and read these into the web browser, and
then download the data into a different Access Table. Four stocks that I am
interested in are: S, MSFT, SBUX, ATT.


The URL would look like this:

http://finance.yahoo.com/q/cq?d=v1&s=s, msft, sbux, att

Then, I would import all the data in that box named 'Basic'. For a long
time I thought this couldn’t be done, and I have seen almost no literature or
documentation on this, but I am now thinking that it may be possible. Any
help would be appreciated.


Ryan---

 

[Danny J. Lesandrini]

I'm not sure if this is it, but the code creates an object from the library
named Microsoft.XMLHTTP

Do you have that on your computer? Frankly, a quick look in the Refrences
dialog didn't help me locate it. Perhaps it's part of the Web Services addin
for Microsoft Access

 

[ryguy7272]

Hummm, I don't know. Maybe my intuition was right; maybe this can't be done.
I know Access is very finicky!!! Whereas Excel will handle any kind of data
you throw at it, Access demands, and I do mean DEMANDS, that data is of a
certain type, or it refuses any request to import (inappropriate data types).
This is good, for data integrity purposes!! I guess the stuff on the Web is
too dynamic and Access developers just said, we don't want people doing
imports into our databases, because we can't be sure what kind of data is
being imported. Is that an appropriate assumption?

Appreciate any feedback.

Regards,
Ryan---