Does anyone have any suggestions on how to load page with specific date?
For example, https://fx2.oanda.com/user/interestrate.html, I need to
manually input
date, which starting from 1/1/1970 to today() or 2/2/2010.
Does anyone have any suggestions on how to load this page into excel?
Thanks in advance for any suggestions
Eric

I only selected US currency although you could select multipl
currencies


Sub OANDA()

URL = "https://fx2.oanda.com/user/interestrate.html"

StartDate = "01/01/1970"
EndDate = "02/02/2010"
MyCurrency = "US DOLLAR"

'Do
' MyStartDate = InputBox("Enter Start Date : ")
'Loop While Not IsDate(Response)

'StartDate = DateValue(MyStartDate)

'Do
' MyEndDate = InputBox("Enter End Date : ")
'Loop While Not IsDate(Response)

'EndDate = DateValue(MyEndDate)

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True


'get web page
IE.navigate2 URL & Ticker
Do While IE.readystate < 4 Or _
IE.busy = True

DoEvents
Loop

Set FormInput = IE.document.getelementsbytagname("input")

FormInput(0).Value = Format(StartDate, "MM/DD/YYYY")
FormInput(1).Value = Format(EndDate, "MM/DD/YYYY")

Set ListBox = IE.document.getelementsbytagname("select")

Set ListboxItem = Nothing
For Each itm In ListBox(3)
If UCase(itm.innertext) = MyCurrency Then
Set ListboxItem = itm
Exit For
End If

Next

If ListboxItem Is Nothing Then
MsgBox ("could Not find currency - exiting Macro ")
Exit Sub
End If

ListboxItem.Selected = True

'submit form
FormInput(2).Select
FormInput(2).Click


Do While IE.readystate < 4 Or _
IE.busy = True

DoEvents
Loop

Set Mon_Sel = IE.document.getelementbyid("mon_sel")



Set Table = IE.document.getelementsbytagname("Table")

RowCount = 1
For Each Row In Table(2).Rows
ColCount = 1
For Each cell In Row.Cells

Cells(RowCount, ColCount) = cell.innertext
ColCount = ColCount + 1
Next cell
RowCount = RowCount + 1
Next Row

IE.Quit
End Su

--
joe
-----------------------------------------------------------------------
joel's Profile: 22
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=17542

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url

Thank you very much for your suggestions

Will it be possible to add a loop for different currencies? such as
Australian Dollar, Japanese Yen, US Dollar, and insert those data into
specific country sheet, Japanese Yen into Japan Sheet, Australian Dollar into
Australian, US Dollar into US sheet etc.
I can copy all the codes and paste one time for Japanese Yen and another
time for Australian Dollar, but it will make this macro very long,
Do you have any suggestions?
Thank you very much for any suggestions
Eric

"joel" wrote:


I only selected US currency although you could select multiple
currencies


Sub OANDA()

URL = "https://fx2.oanda.com/user/interestrate.html"

StartDate = "01/01/1970"
EndDate = "02/02/2010"
MyCurrency = "US DOLLAR"

'Do
' MyStartDate = InputBox("Enter Start Date : ")
'Loop While Not IsDate(Response)

'StartDate = DateValue(MyStartDate)

'Do
' MyEndDate = InputBox("Enter End Date : ")
'Loop While Not IsDate(Response)

'EndDate = DateValue(MyEndDate)

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True


'get web page
IE.navigate2 URL & Ticker
Do While IE.readystate < 4 Or _
IE.busy = True

DoEvents
Loop

Set FormInput = IE.document.getelementsbytagname("input")

FormInput(0).Value = Format(StartDate, "MM/DD/YYYY")
FormInput(1).Value = Format(EndDate, "MM/DD/YYYY")

Set ListBox = IE.document.getelementsbytagname("select")

Set ListboxItem = Nothing
For Each itm In ListBox(3)
If UCase(itm.innertext) = MyCurrency Then
Set ListboxItem = itm
Exit For
End If

Next

If ListboxItem Is Nothing Then
MsgBox ("could Not find currency - exiting Macro ")
Exit Sub
End If

ListboxItem.Selected = True

'submit form
FormInput(2).Select
FormInput(2).Click


Do While IE.readystate < 4 Or _
IE.busy = True

DoEvents
Loop

Set Mon_Sel = IE.document.getelementbyid("mon_sel")



Set Table = IE.document.getelementsbytagname("Table")

RowCount = 1
For Each Row In Table(2).Rows
ColCount = 1
For Each cell In Row.Cells

Cells(RowCount, ColCount) = cell.innertext
ColCount = ColCount + 1
Next cell
RowCount = RowCount + 1
Next Row

IE.Quit
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=175422

Microsoft Office Help

.

I made a few changes and the code now dumps everything

Sub OANDA()

URL = "https://fx2.oanda.com/user/interestrate.html"

StartDate = "01/01/1970"
EndDate = "02/02/2010"
MyCurrency = "US DOLLAR"

'Do
' MyStartDate = InputBox("Enter Start Date : ")
'Loop While Not IsDate(Response)

'StartDate = DateValue(MyStartDate)

'Do
' MyEndDate = InputBox("Enter End Date : ")
'Loop While Not IsDate(Response)

'EndDate = DateValue(MyEndDate)

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True


'get web page
IE.Navigate2 URL & Ticker
Do While IE.readyState < 4 Or _
IE.Busy = True

DoEvents
Loop

Set FormInput = IE.document.getElementsByTagName("input")

FormInput(0).Value = Format(StartDate, "MM/DD/YYYY")
FormInput(1).Value = Format(EndDate, "MM/DD/YYYY")

Set ListBox = IE.document.getElementsByTagName("select")
Set AllCurrencies = ListBox(3).getElementsByTagName("option")
Set ListboxItem = Nothing
For Each itm In ListBox(3)
itm.Selected = True
'If UCase(itm.innertext) = MyCurrency Then
'Set ListboxItem = itm
'Exit For
'End If

Next itm

'If ListboxItem Is Nothing Then
'MsgBox ("could Not find currency - exiting Macro ")
'Exit Sub
'End If

'ListboxItem.Selected = True

'submit form
FormInput(2).Select
FormInput(2).Click


Do While IE.readyState < 4 Or _
IE.Busy = True

DoEvents
Loop

Set Mon_Sel = IE.document.getElementById("mon_sel")



Set Table = IE.document.getElementsByTagName("Table")

RowCount = 1
For Each Row In Table(2).Rows
ColCount = 1
For Each cell In Row.Cells

Cells(RowCount, ColCount) = cell.innertext
ColCount = ColCount + 1
Next cell
RowCount = RowCount + 1
Next Row

IE.Quit
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=175422

Microsoft Office Help