Anything analogous to querytable in Access VBA 1

[griswom]

I would like to be able to connect to a URL in access and store the response in a variable to display on a report.

I use this code in Excel:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;

http://finance.yahoo.com/d/quotes.csv?f=l1&s="

& [A3], _
Destination:=Range("p32"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True

The response is a price quote from Yahoo Finance. I would like to store that to a variable in Access VBA. Any help is appreciated.

Mike

 

[crobg]

I can't find one.

You can though link the spreadsheet as a table. Then right a function that querys the table and returns the value for your report.

Function StockQuote()

Dim db As Database, rs As Recordset, strSQL As String

strSQL = "Select * FROM Sheet1" 'You can use SQL from an Access QBE query.

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

StockQuote = rs![Field1]

rs.Close
db.Close

End Function

 

[griswom]

crobg,

Thanks for the advice. Sorry for the belated reply. Here is the code I ended up using in case anyone else needs this. It requires the XML 5.0 library:

Function XMLPrice(sSecurity As String)

Dim xml As XMLHTTP
Dim sURL As String
Dim sResponse As String
Dim sQuote As String

Set xml = CreateObject("Microsoft.XMLHTTP")

sURL = "

http://finance.yahoo.com/d/quotes.xml?f=l1&s="

& sSecurity

xml.Open "GET", sURL, False

xml.send ("")

sResponse = xml.responseText

sQuote = Left(sResponse, (InStr(sResponse, ".") + 2))

XMLPrice = sQuote

Griz

 

[crobg]

I a possible project that will use XML. Is there a help file on the XML 5.0 library that you know about?

 

[griswom]

crobg,

Haven't been able to find a good overall reference. I just pieced my code together from examples I found on Google. Sorry I can't be of more help.

Griz