score:3

Sub UseDynamicURL()
    Dim wb As Workbook
    Dim src As Worksheet
    Dim tgt As Worksheet

    Set wb = ThisWorkbook
    Set src = wb.Sheets("Sheet1")
    Set tgt = wb.Sheets("Sheet2")

    Dim url As String
    Dim symbol As String

    symbol = src.Range("A1")
    url = "URL;http://finance.yahoo.com/q/is?s="
    url = url & symbol & "+Income+Statement&annual"

    With tgt.QueryTables.Add(Connection:= _
        url, _
        Destination:=tgt.Range("A1"))

        .BackgroundQuery = True
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With
End Sub

Related Articles