• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help make recorded macro work (to obtain currency exchange rates)

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:

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
 
Hi !

As a beginner starter from my recorder :​
Code:
Sub Macro1()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.floatrates.com/daily/usd.xml", Destination:=Range("A1"))
        .PreserveFormatting = False
        .RefreshStyle = xlOverwriteCells
        .SaveData = True
        .AdjustColumnWidth = False
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .Refresh BackgroundQuery:=False
        .Delete
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
Here is a Currency Exchange Template that I use. It employs Power Query. To update it daily, click on the Data-->Refresh Button twice. Sometimes Three Times.
 

Attachments

  • Currency.xlsm
    31.2 KB · Views: 3
Back
Top