Exchange Rates from Web Into Table

healey21

Well-known Member
Joined
Dec 22, 2009
Messages
900
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. ;)
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,266
Office Version
  1. 365
Platform
  1. Windows
Why can't you get the data in Excel and then put it in the table?
 

healey21

Well-known Member
Joined
Dec 22, 2009
Messages
900
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,266
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,315
Messages
5,623,950
Members
416,001
Latest member
teabag

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top