Nov 20, 2022

# Solution 1 :

What a challenge! I am certainly not the least experienced person, but certainly not the best either.

Already when I read your text, it was clear to me that these are HTML events. I already knew the site, but I didn’t know what to expect. I invested several hours now, but in the end I cracked it.

The following macro with the additional Sub() solves your problem. For more information, please refer to the comments in the macro. The solution was very difficult for me, but I did not learn anything, because all knowledge was there. But not in this way.

In the end everything seems to be simple. You wouldn’t believe how many combinations of events I tried.

Take this macro, it works:

``````Sub OandaCurrencyConverter()

Dim ie As Object
Dim doc As Object
Dim nodeCurrencyDropdown As Object
Dim nodeAllCurrencies As Object
Dim nodeOneCurrency As Object
Dim starttime As Double
Dim Curr As String
Dim row As Long
Dim i As Byte
Dim leftRightIdentifier As String
Dim myws As Worksheet

starttime = Timer
Set myws = ThisWorkbook.Worksheets("Ui")
myws.Range(myws.Cells(4, 9), myws.Cells(myws.Rows.Count, 9)).ClearContents 'Delete previous results

'Initialize Internet Explorer, set visibility,
'call URL and wait until page is fully loaded
Set ie = CreateObject("internetexplorer.application")
ie.Visible = False
ie.navigate "https://www1.oanda.com/currency/converter/"
Do Until ie.readyState = 4: DoEvents: Loop
Set doc = ie.document

'Get results
Do While myws.Cells(4 + row, 4).Value <> ""
'ENTERING VALUE TO BE CONVERTED
'If this value is entered first, the desired result is calculated
'automatically when the currencies are set in the dropdowns
doc.getElementById("quote_amount_input").Value = myws.Cells(4 + row, 8).Value

'ENTERRING CURRENCIES
For i = 0 To 1
If i = 0 Then
'Left currency
leftRightIdentifier = "quote"
Curr = myws.Cells(4 + row, 5).Value
Else
'Right currency
leftRightIdentifier = "base"
Curr = myws.Cells(4 + row, 7).Value
End If

'Get the needed dropdown
Set nodeCurrencyDropdown = doc.getElementById(leftRightIdentifier & "_currency_list_container")

'Generate node collection of all currencies in dropdown
Set nodeAllCurrencies = nodeCurrencyDropdown.getElementsByClassName("ltr_list_item")

'Search the wanted currency in the single nodes
For Each nodeOneCurrency In nodeAllCurrencies
If InStr(1, nodeOneCurrency.innerText, Curr) > 0 Then
Call TriggerEvent(doc, nodeOneCurrency, "mouseover")
nodeOneCurrency.Click
Exit For
End If
Next nodeOneCurrency
Next i

'Give a little time to calculate and get the result
Application.Wait (Now + TimeValue("0:00:02"))
myws.Cells(4 + row, 9).Value = doc.getElementById("base_amount_input").Value * 1

'Next row
row = row + 1
Loop

'Clean up
ie.Quit
Set ie = Nothing
Set doc = Nothing
Set nodeCurrencyDropdown = Nothing
Set nodeAllCurrencies = Nothing
Set nodeOneCurrency = Nothing

'Show needed time
MsgBox "Currencies have been converted" & vbNewLine & "Time Taken - " & Format((Timer - starttime) / 86400, "hh:mm:ss")
End Sub
``````

And this Sub() to trigger HTML events:

``````Private Sub TriggerEvent(htmlDocument As Object, htmlElementWithEvent As Object, eventType As String)

Dim theEvent As Object

htmlElementWithEvent.Focus
Set theEvent = htmlDocument.createEvent("HTMLEvents")
theEvent.initEvent eventType, True, False
htmlElementWithEvent.dispatchEvent theEvent
End Sub
``````

# Problem :

I’m trying to select currency from a list on this website : https://www1.oanda.com/currency/converter/

The problem is value gets entered into those fields, the selection is basically the currencies but it gets refreshed when we enter it manually. Through the macro, value gets entered but the javascript or whatever the background scene to convert the values doesn’t happen. I cannot use any other website for currency conversion. Any help would be highly appreciated.

currency values(in excel worksheet) are derived to curr1,curr2 variables

HERE IS THE CODE

``````'Option Explicit
Sub converter()

Dim ie As Object
Dim doc As HTMLDocument
Dim inputval, returnval As String
Dim starttime As Double
starttime = Timer
Dim Curr1, Curr2 As String
Dim i As Integer
Dim mywb As Workbook
Dim myws As Worksheet
Set mywb = ThisWorkbook
Set myws = mywb.Worksheets("Ui")

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

'Navigating to the URL
ie.navigate "https://www1.oanda.com/currency/converter/"

'Letting the browser fully load
Do While ie.Busy Or ie.readyState <> 4
Application.Wait DateAdd("s", 1, Now)
Loop

Set doc = ie.document
Range(Cells(4, 9), Cells(Rows.Count, 9)).ClearContents

Do While myws.Cells(4 + i, 4).Value <> ""

Curr1 = myws.Cells(4 + i, 5).Value
Curr2 = myws.Cells(4 + i, 7).Value
inputval = myws.Cells(4 + i, 8).Value

'ENTERRING CURRENCY VALUES
doc.getElementById("quote_currency_input").Value = Curr1
doc.getElementById("base_currency_input").Item.innerText = Curr2

'ENTERING VALUE TO BE CONVERTED
returnval = doc.getElementById("base_amount_input").Value

'Do While IE.Busy Or IE.readyState <> 4
Application.Wait (Now + TimeValue("0:00:05"))
'Loop

myws.Activate
myws.Cells(4 + i, 9).Value = returnval

i = i + 1
Loop

'IE.Quit
'MsgBox "Currencies have been converted" & vbNewLine & "Time Taken - " & Format((Timer - starttime) / 86400, "hh:mm:ss")
End Sub
``````

# Comments

### Comment posted by Rishabh Arora

Hello, I have tested it and it worked flawlessly. Thank you very much for providing the solution and I appreciate your efforts very much. You are truly a genius, this was my first question here and I am glad that I got a response quickly and completely according to my expectations. I understand and value the efforts and time you have spent of making it work by trying numerous combination of events and possible ways. Thank you very much! ðŸ™‚