Excel VBA - downloading multiple historical exchange rates

0 votes

In an effort to automatically provide a variety of currency rates for a user-specified time period, I've been working on a table. I've been attempting to modify the VBA code to incorporate several currency conversions since I came across this article, which I thought to be quite helpful. But I can't seem to figure out how to do this and I keep getting the following error:

Error 1004: Microsoft Office Excel can convert only one column at a time. The range can be many rows tall but no more than one column wide. Try again by selecting cells in one column only.

Could you please have a look at my code below and help me resolve the error so that I can obtain multiple currency conversions? Many thanks in advance.

Sub GetData()
    Dim DataSheet As Worksheet
    Dim endDate As String
    Dim startDate As String
    Dim str As String
    Dim LastRow As Integer

    Sheets("GBP").Cells.Clear

    Set DataSheet = ActiveSheet

    startDate = DataSheet.Range("startDate").Value
    endDate = DataSheet.Range("endDate").Value

    ' GBP/EUR

    str = "http://www.oanda.com/currency/historical-rates/download?quote_currency=" _
    & "GBP" _
    & "&end_date=" _
    & Year(endDate) & "-" & Month(endDate) & "-" & Day(endDate) _
    & "&start_date=" _
    & Year(startDate) & "-" & Month(startDate) & "-" & Day(startDate) _
    & "&period=daily&display=absolute&rate=0&data_range=c&price=bid&view=table&base_currency_0=" _
    & "EUR" _
    & "&base_currency_1=&base_currency_2=&base_currency_3=&base_currency_4=&download=csv"

    With Sheets("GBP").QueryTables.Add(Connection:="URL;" & str, Destination:=Sheets("GBP").Range("A1"))
        .BackgroundQuery = True
        .TablesOnlyFromHTML = False
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With

    Sheets("GBP").Range("A5").CurrentRegion.TextToColumns Destination:=Sheets("GBP").Range("A5"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=Array(1, 2)

    Sheets("GBP").Columns("A:B").ColumnWidth = 12
    Sheets("GBP").Range("A1:B2").Clear

    LastRow = Sheets("GBP").UsedRange.Row - 6 + Sheets("GBP").UsedRange.Rows.Count

    Sheets("GBP").Range("A" & LastRow + 2 & ":B" & LastRow + 5).Clear


    ' GBP/USD

    str = "http://www.oanda.com/currency/historical-rates/download?quote_currency=" _
    & "GBP" _
    & "&end_date=" _
    & Year(endDate) & "-" & Month(endDate) & "-" & Day(endDate) _
    & "&start_date=" _
    & Year(startDate) & "-" & Month(startDate) & "-" & Day(startDate) _
    & "&period=daily&display=absolute&rate=0&data_range=c&price=bid&view=table&base_currency_0=" _
    & "USD" _
    & "&base_currency_1=&base_currency_2=&base_currency_3=&base_currency_4=&download=csv"

    With Sheets("GBP").QueryTables.Add(Connection:="URL;" & str, Destination:=Sheets("GBP").Range("C1"))
        .BackgroundQuery = True
        .TablesOnlyFromHTML = False
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With

    Sheets("GBP").Range("C5").CurrentRegion.TextToColumns Destination:=Sheets("GBP").Range("C5"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=Array(1, 2)

    Sheets("GBP").Columns("C:D").ColumnWidth = 12
    Sheets("GBP").Range("C1:D2").Clear

    LastRow = Sheets("GBP").UsedRange.Row - 6 + Sheets("GBP").UsedRange.Rows.Count

    Sheets("GBP").Range("C" & LastRow + 2 & ":D" & LastRow + 5).Clear

End Sub

The error occurs at the following line:

Sheets("GBP").Range("C5").CurrentRegion.TextToColumns Destination:=Sheets("GBP").Range("C5"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=Array(1, 2)
Oct 17, 2022 in Others by Kithuzzz
• 38,000 points
628 views

1 answer to this question.

0 votes

Do columns C and D contain your raw data? If so, you might need to arrange the data differently. You could either concatenate the two into column C with a comma to separate them (because this is the delimiter used here) or place the information in column D of a distinct row in column C. After that, you must remove the:

.CurrentRegion

after

Sheets("GBP").Range("C5")
answered Oct 18, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Searching Multiple Criteria In Large Data Set to make new Data Set Excel VBA

Copy the data to an array, filter ...READ MORE

answered Jan 31, 2023 in Others by narikkadan
• 63,600 points
1,074 views
0 votes
1 answer

Excel VBA compare values on multiple rows and execute additional code

I would use a Dictionary & Collection ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,600 points
1,908 views
0 votes
1 answer
0 votes
1 answer

Language independent way to get "My Documents" folder in VBA Excel 2003

 Hello :)  This code may help you in your ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
832 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,259 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,696 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
971 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,089 views
0 votes
1 answer

Excel vba auto log in to hdfc bank and downloading the bank statement

That page has a bunch of nested ...READ MORE

answered Jan 6, 2023 in Others by narikkadan
• 63,600 points
655 views
0 votes
1 answer

How to do multiple transpose in excel vba

Use Dictionary object Sub test2() With New ...READ MORE

answered Jan 22, 2023 in Others by narikkadan
• 63,600 points
677 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP