1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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)

Discussion in 'VBA Macros' started by Carlos Ruano, Mar 14, 2019.

  1. Carlos Ruano

    Carlos Ruano Member

    Messages:
    60
    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 (vb):

    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 (vb):

    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
  2. Marc L

    Marc L Excel Ninja

    Messages:
    4,274
    Hi !

    As a beginner starter from my recorder :​
    Code (vb):
    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 !
    Carlos Ruano likes this.
  3. Carlos Ruano

    Carlos Ruano Member

    Messages:
    60
    Awesome! Thank you, Marc!
  4. AlanSidman

    AlanSidman Active Member

    Messages:
    459
    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.

    Attached Files:

Share This Page