CODEWITHSUNDEEP
urlexcelexcel-web-queryvba
user2740589
user2740589

Reputation: 21

Create dynamic url using value from cell

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

Upvotes: 2

Views: 17272

Answers (1)

Jon Crowell
Jon Crowell

Reputation: 22338

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

Upvotes: 3

Related Questions