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

Sabtu, 13 Juli 2013

Re: [MS_AccessPros] How to get online data

Dear John,
Thank you so much for your time. I will try this out, and let you know.
Best Reards,
Kevin

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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
>