Visit website page from Array data, Retrieve ElementID, populate new array

Question

OBJECTIVE

Pull in exchange rate data for various currencies.

APPROACH

Select Active Sheet and copy currencies-to-be-converted into an array (e.g ["EUR", "GBP", "USD"]
Open Browser and visit currency conversion website
Loop through different currencies and extract currency conversion factors
Append conversion factors to an array
Repopulate excel with newest conversion factors

CODE

Sub retreiveCurrencies()

Dim ws As Worksheet
Dim locals() As Variant
Dim exchangeArray() As Variant
Dim i As Long
Dim IE As Object


'Select currencies to convert
Sheets("APPENDIX - CURRENCY CONVERTER").Activate
locals = Array(ActiveSheet.Range("B2:B15"))
'This should return locals = ["EUR", "GBP, "USD"]

'Prep Internet Explorer
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False

Do While IE.Busy And Not IE.readyState = READYSTATE_COMPLETE
    DoEvents
Loop

'Loop through currencies and retreive exchange rates. Paste rates into exchangeArray
For i = LBound(locals()) To UBound(locals())
    IE.Navigate "http://www.usforex.com/currency-converter/" & locals(i) & "/usd/1.00/false"
    exchange = IE.Document.GetElementById("converterToAmount").innerText
    'exchangeArray = [] QUESTION: What should I add here to create an array of exchange rates?
Next i

'Paste exchange rate array into currency conversion column
ActiveSheet.Range("D2:D15") = exchangeArray()


End Sub

QUESTIONS/ISSUE(S)

For line IE.Navigate "http://www.usforex.com/currency-converter/" & locals(i) & "/usd/1.00/false" I receive a "Type Mismatch". Do I need to convert locals() to text?
How do I loop through IE.Document.GetElementById("converterToAmount").innerTextand append retrieved values to a new array exchangeArray()

NOTE: There may be many errors in this code. I am a VBA newbie and this is my attempt at a more complex script.

Comintern Comintern · Accepted Answer · 2016-09-26T16:12:04

The issue is this line:

locals = Array(ActiveSheet.Range("B2:B15"))

This isn't creating an array of values from ActiveSheet.Range("B2:B15) - it is creating an array of Range objects with only one element in it. This means that in the line of code that actually throws the mismatch, locals(i) is a Range. Since you have multiple cells in that Range, the default member (.Value) returns a 2D array of Variant, which can't be concatenated into a string.

You need something more like this instead:

locals = ActiveSheet.Range("B2:B15").Value

'...

'Loop through currencies and retreive exchange rates. Paste rates into exchangeArray
For i = LBound(locals, 1) To UBound(locals, 1)
    ie.navigate "http://www.usforex.com/currency-converter/" & locals(i, 1) & "/usd/1.00/false"
    exchange = ie.document.getElementById("converterToAmount").innerText
    'exchangeArray = [] QUESTION: What should I add here to create an array of exchange rates?
Next i

Visit website page from Array data, Retrieve ElementID, populate new array

1 Answers