How do I dynamically change the url to

[Eslava]

I'm trying to pull the data out of various tables from a website.

The various tables designated by the year of the data.

I was trying to designate a cell that would have the year of the data I want.

I keep running into an error and I'm sure it has to do with the following:
Connection:="URL;http://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yieldYear&year=" & Sheet.Range("D7"), _
Destination:=Range("A1"))

How do I fix this?

BTW, here's the full VBA code in case this helps:

Sub GetTreasuryRates()
Sheets.Add After:=ActiveSheet
Dim qt As QueryTable
Dim ws As Worksheet
'using a worksheet variable means autocompletion works better
Set ws = ActiveSheet
'set up a table import (the URL; tells Excel that this query comes from a website)
Set qt = ws.QueryTables.Add( _
Connection:="URL;http://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yieldYear&year=" & ActiveSheet.Range("D7"), _
Destination:=Range("A1"))
qt.RefreshOnFileOpen = True
qt.Name = "RecentTreasuryRates"
qt.FieldNames = True
qt.WebSelectionType = xlSpecifiedTables
qt.WebTables = "67"
'import the data
qt.Refresh BackgroundQuery:=True
End Sub