Is it possible to pull Internet stock data directly into Access ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What I mean is using Access VBA (as opposed to VB) - I've been searching
through the active pages documentation but can't seem to find anything that
will allow me to query a website (quote.yahoo.com) and pull the returned data
directly into a temp database.

Thanks !

John Gould
jfngould_@_hotmail_._com (remove underscores)
 
Hi
Are Smart Tags available to forms in Access ? In Reading "Developing Smart
Tag Solutions" in Microsoft MSDN Library, under the Availability and Usage
paragraph :

Smart tags are available in Microsoft Word 2002, Microsoft Excel 2002,
Microsoft Outlook® 2002 (when Word 2002 is enabled as the e-mail editor for
Outlook), and Microsoft Internet Explorer (provided that Word 2002 or Excel
2002 is installed).

If so, then when is the xml file loaded ? I can certainly create either an
smart tag xml file or smart tag dll file, filling in the tags from the
database, but I'd guess this has to be accomplished before the form is opened
? I'd guess in this case it would be best to open the main form from a
dashboard and use the dashboard to load the xml or dll file before opening
the main form. Yes ?

Thanks for any insite you can provide.
 
John,

If you can get them through a web service try looking at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnxpwst/html/odc_ofwsrt.asp

Details of how to set up a proxy in VBA (kind of as you would in VB.NET).

If you are looking at a 'static' html page (could be ASP/ASPX) you can set
references to use

Microsoft WinHHTP Services
Microsoft HTML Object Browser

I'll paste in my code that uses these - but my source has a very simple
tabular layout (and is behind a firewall - so I've junked the URL). This
method would get quite involved if you are trying to get data from something
convoluted. In theory though you can get what you like if it is visible to
you through a browser.

Regards,

Chris.

Dim db As DAO.Database
Dim rsResults As DAO.Recordset
Dim objWinHTTP As WinHttp.WinHttpRequest 'Handles the actual
HTTP request
Dim sURL As String
Dim objHTMLDoc As Object 'Need to late bind
this as Microsoft is ...
Dim objEleTables As MSHTML.IHTMLElementCollection '
Dim objEleRows As MSHTML.IHTMLElementCollection
Dim objHTMLRow As MSHTML.HTMLTableCell
Dim objEleCells As MSHTML.IHTMLElementCollection
Dim objHTMLCell As MSHTML.HTMLTableCell
Dim objHTMLTable As MSHTML.HTMLTable

Dim lRowPointer As Long
Dim lColPointer As Long

'Set up db
Set db = CurrentDb
Set rsResults = db.OpenRecordset("SELECT * FROM tblUnderlyingData")

'Set up HTTP call
sURL = "http://" 'Your GET string here - can use a POST but would have
to construct a header

Set objWinHTTP = New WinHttp.WinHttpRequest

'No security so no paste required
objWinHTTP.Open "GET", sURL
objWinHTTP.Send

Set objHTMLDoc = New MSHTML.HTMLDocument
objHTMLDoc.write objWinHTTP.ResponseText

'Get the collection of tables in the HTML
Set objEleTables = objHTMLDoc.body.getElementsByTagName("table")

'Get the first (only) table in the output
Set objHTMLTable = objEleTables(0)

'body of table
Set objEleRows = objHTMLTable.getElementsByTagName("tr")

For lRowPointer = 0 To objEleRows.length - 1

Set objHTMLRow = objEleRows.Item(lRowPointer)

If lRowPointer = 0 Then

'Ignore the first row - it is the headings row

Else

Set objEleCells = objHTMLRow.getElementsByTagName("td")

'Add a new reults record for each row
With rsResults

.AddNew
.Fields("COB").Value = dCOB
.Fields("underlyingid").Value = iUnderlyingID

'add the fields in order
For lColPointer = 0 To objEleCells.length - 1

Set objHTMLCell = objEleCells.Item(lColPointer)

.Fields(lColPointer + 2).Value = objHTMLCell.innerText

Next lColPointer

.Update

End With

End If

Next lRowPointer
 
Back
Top