Using string for Web Query connection

[Roderick_E]

I can't figure out why I can't make a dynamic string for this web query. It is supposed to update an existing query table with a different currency code. If I change the code manually it works. If I remove the roestr from the red line and simply change the exchange code, gbp to eur or jpy or something, it works but if I try to control the code with the roestr method I get popup error 1004 on the .connection line. Any ideas?

Sub docurquery()
Dim roestr As String
roecode = Sheet2.Range("D1") 'any currency code such as EUR
roestr = """URL;http://www.x-rates.com/table/?from=" & roecode & "&amount=1"""
    With ActiveWorkbook.Connections("Connection")
        .Name = "Connection"
        .Description = ""
    End With
    Sheet1.Activate
    Sheet1.Range("A:C").Select
    With Selection.QueryTable
        .Connection = roestr [B][COLOR=#ff0000]'"URL;http://www.x-rates.com/table/?from=gbp&amount=1"[/COLOR][/B]
        '.CommandType = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Connections("Connection").Refresh
    
End Sub

 

[Roderick_E]

SOLVED:

It didn't want the quotes:

Sub docurquery()
Dim roestr As String
Dim roecode As String
roecode = Sheet2.Range("D1") 'any currency code such as EUR
roestr = [COLOR=#ff0000]"URL;http://www.x-rates.com/table/?from=" & roecode & "&amount=1"[/COLOR]
    With ActiveWorkbook.Connections("Connection")
        .Name = "Connection"
        .Description = ""
    End With
    'MsgBox roestr
    'Exit Sub
    Sheet1.Activate
    Sheet1.Range("A:C").Select
    With Selection.QueryTable
        .Connection = roestr '"URL;http://www.x-rates.com/table/?from=gbp&amount=1"
        '.CommandType = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Connections("Connection").Refresh
    
End Sub