ACCESS FEED: RE: [MS_AccessPros] How to get online data

Jumat, 12 Juli 2013

RE: [MS_AccessPros] How to get online data

Kevin-

You can sometimes use Excel as an intermediary to "scrape" data off a
website. Be forewarned that websites - especially ones that offer a
subscription for all their data instead of just a snapshot - don't like this
and often keep changing their site in ways that will disable what you've
built.

I built one for a client that grabs Canadian Dollar exchange rates off
XE.com. In a new spreadsheet, I built an external data connection to:

http://www.xe.com/currencytables/?from=CAD&date=2013-07-05

.. and saved the result in a .iqy file.

I then added three parameters: YearNum, MonthNum, and DayNum. You have to
do this by editing the .iqy file you saved in Notepad. When I was done, it
looked like:

WEB
1
http://www.xe.com/currencytables/?from=CAD&date=["YearNum"]-["MonthNum"]-["D
ayNum"]

Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

Next, I removed the connection I created with the Wizard (you have to do
this so it will pick up your new definition) and cleared the sheet. I typed
a year value, month value, and day value in three different cells (A1, A2,
and A3 for example), selected a cell below that, then went back to
Connections and re-added my edited query. I then selected the query and
clicked Properties. Under Definition, I clicked Parameters. I selected
each parameter in turn, chose Get the Value From the Following Cell, clicked
in the cell window, then clicked the cell on the sheet containing that
parameter. I closed the Properties window and closed the Workbook
Connections window. Clicked Existing Connections, then clicked Open. After
selecting a target cell below the three parameter cells, I clicked OK.

You should now see the data from the web page imported. Scroll down, and
you should find the table that lists the exchange rate on the selected day
from Canadian Dollar to all the currencies available on that website. You
can then create a Named Range on that part of the worksheet and then go to
Access and link the range as a table to get the data into Access.

To automate the entire procedure, I wrote some code:

-------------------------------------------------------

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim dblTimer As Double
Dim db As DAO.Database
Dim blnReset As Boolean

DoCmd.Hourglass True

' Hide my subform
Me.fsubRates.Visible = False
' Point to this database
Set db = CurrentDb
' Open Excel
Set xlApp = CreateObject("Excel.Application")
' Open the workbook
Set xlBook = xlApp.Workbooks.Open(CurrentProject.Path & "\Canadian
Dollar Exchange.xlsx")
' Point to the sheet
Set xlSheet = xlBook.Worksheets("Sheet1")
' Set the month
xlSheet.Range("A1").Value = CLng(Month(Date))
' Set day
xlSheet.Range("A2").Value = CLng(Day(Date))
' Set year
xlSheet.Range("A3").Value = CLng(Year(Date))
' Refresh
xlBook.RefreshAll

blnReset = True

' Wait for it to finish
dblTimer = Timer
Do Until (Timer - dblTimer) > 5
DoEvents
Loop
' Save the updated result
xlBook.Save
' Close out
xlBook.Close
xlApp.Quit

' Delete the old linked table
db.TableDefs.Delete "zLinkedRates"

' Re-import the spreadsheet
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml,
"zLinkedRates", _
CurrentProject.Path & "\Canadian Dollar Exchange.xlsx", True, _
"Canadian_Dollar_Exchange_Rates"

' Append the data
db.Execute "INSERT INTO ztblExchangeRates ([Currency Unit], [Currency
Name], " & _
"[Units Per CAD], [CAD Per Unit], [Effective Date]) " & _
"SELECT [Currency Unit], F2, [Units Per CAD], [CAD Per Unit], #" &
Now() & _
"# As EffectiveDate FROM zLinkedRates " & _
"WHERE [Currency Unit] = 'USD'", dbFailOnError
' Requery my subform
Me.fsubRates.Requery
' .. and unhide it
Me.fsubRates.Visible = True

' Call EmailAdmins(True)

Exit_Procedure:

DoCmd.Hourglass False

If blnReset Then
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
Set db = Nothing
End If
Exit Sub

Error_Handler:

Call EmailAdmins(False, Err.Number, Err.Description)
Resume Exit_Procedure
----------------------------------------------------

The code dynamically opens the spreadsheet containing the data link, sets
the year, month, and day values, then refreshes the data. It then imports
(no link) the data and grabs the USD rate the client wanted to append to a
historical table.

Obviously, you can change the target currency to anything you want - USD for
US Dollar, EUR for European Union Euro, etc.

Have fun...

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Qing Qing
Sent: Saturday, July 13, 2013 3:04 AM
To: ms_access_professionals@yahoogroups.com
Subject: [MS_AccessPros] How to get online data

Dear All,
I want to get online data to my access application, for example, there's
currency exchange rate online, I want to make a form in access to be able to
see the rate updated from online. Is this possible? Thanks.
Best Regards,
Kevin

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links