Carlos Ruano
Member
Hi All,
I am hoping that someone can help me make the code below work. I used the macro recorder to create it, but when I try to run it later, it fails at
.CommandType = 0
Run-time error '5': Invalid procedure call or argument
Here is the recorded macro:
I used instructions on the following site when recording the above macro:
https://office-watch.com/2016/excel-easily-get-the-latest-currency-exchange-rates/
If you have a different solution that would allow me to obtain currency exchange rates, that would also be fine. I would really like it to be run in a macro if possible.
I did find the following function:
BUT ... I don't know how to make it work. Should I put it in a module like a sub procedure? I tried that, and it didn't work.
Any help would be greatly appreciated!
Carlos
I am hoping that someone can help me make the code below work. I used the macro recorder to create it, but when I try to run it later, it fails at
.CommandType = 0
Run-time error '5': Invalid procedure call or argument
Here is the recorded macro:
Code:
Sub FXRate()
'
' FXRate Macro
'
'
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;http://www.floatrates.com/daily/usd.xml", Destination:=Range("$A$1"))
.CommandType = 0
.Name = "usd"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 600
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "FX Rates"
End Sub
I used instructions on the following site when recording the above macro:
https://office-watch.com/2016/excel-easily-get-the-latest-currency-exchange-rates/
If you have a different solution that would allow me to obtain currency exchange rates, that would also be fine. I would really like it to be run in a macro if possible.
I did find the following function:
Code:
Public Function wConvertCurrency(currency1, currency2)
Dim yahooHTTP As New WinHttp.WinHttpRequest
yahooHTTP.Open "GET", "http://download.finance.yahoo.com/d/quotes.csv?s=" & currency1 & currency2 & "=X&f=l1"
yahooHTTP.send
wConvertCurrency = CDbl(yahooHTTP.ResponseText)
End Function
BUT ... I don't know how to make it work. Should I put it in a module like a sub procedure? I tried that, and it didn't work.
Any help would be greatly appreciated!
Carlos