Exchange Rates from Web Into Table

[healey21]

I am looking to use Access VBA to pull an Exchange rate table from a website into tblExchangeRates from this website, the table is on the right.

http://www.oanda.com/

But don't know the code.

I did record a macro in Excel to get the code but have been told it has to go into an exchange rate table in the database. The code I recorded is shown below:

Sub Macro1()
'
' Macro1 Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.oanda.com", _
Destination:=Range("$A$1"))
.Name = "www.oanda"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 60
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub

Can anyone guide me please.

 

[Norie]

Why can't you get the data in Excel and then put it in the table?

 

[healey21]

Hi Norie,

I think that has to be the way to go as I can't see any way to do this direct from the Internet.

Thank you for your comments.

 

[jackd]

see http://www.utteraccess.com/forum/Exchange-Rate-Web-Site-t1976000.html
for a similar post and solution

 

[Norie]

healey21

If you want code that will extract data directly from the page I can take a look.

I'd need to know what you are looking for though.

On the page the link you posted there's only a small table with the rates for USD, GBP, EUR, and JPY.

Is that what you want or is it other specific exchange rates or currencies?