How to programmatically perform a web query

[ikjohnson]

From the Microsoft article 213730, comes the following code for a static web query: (I've modified the URL only.)

Sub URL_Get_Query()

   With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://au.finance.yahoo.com/q?m=a&s=AGL.AX+AMP.AX&d=v1",    Destination:=Range("a1"))

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

This successfully returns the tables on the found web page back into Excel.
However, the URL will change frequently. I've used the following code to test access to the web with a changing URL:

Sub URL_Only2()

ActiveWorkbook.FollowHyperlink Address:= _
        [D1].Value, NewWindow:=False, _
        AddHistory:=True

End Sub

where the value of cell D1 is the changing URL.
What I'm trying to do is substitute the [D1].Value into the first lot of code. How could this be done?

 

[Tazguy37]

Is D1 the entire URL? If so, try:

With ActiveSheet.QueryTables.Add(Connection:= _
Range("D1").Value, Destination:=Range("a1"))

Hope that helps!

 

[ikjohnson]

Yes, D1 is the entire URL - and it works! I just didn't have the syntax right before.

Thank you.