Create dynamic url using value from cell

Question

I am running a web query for an Excel sheet and have been able to pull data correctly however I want to reference a cell that can be changed in the worksheet.

Ex. I want to make the F reference a cell so I can easily change the request without changing the code. I've been trying to use a range function.

Sub URL_Get_Query()

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://finance.yahoo.com/q/is?s=F+Income+Statement&annual", _
    Destination:=Range("a1"))

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

score 3 · Answer 1 · answered Sep 02 '13 at 17:30

This will take whatever symbol you have in cell A1 of Sheet1 and use it in the query. The results are written to cell A1 on Sheet2. You can modify this to use different ranges and write to different sheets if you need to.

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

Create dynamic url using value from cell

1 Answers1