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

make the title clearer

dzada
– dzada

2013-09-02 17:24:24 +00:00
Commented Sep 2, 2013 at 17:24 — dzada
– dzada, Commented Sep 2, 2013 at 17:24

Jon Crowell · Accepted Answer · 2013-09-02 17:30:08Z

3

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

answered Sep 2, 2013 at 17:30

Jon Crowell

22.4k15 gold badges94 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create dynamic url using value from cell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related