Excel 2010 Web Query with connection string from a cell

How to create a Web Query in Excel 2010 with the connection string (url) from a cell? For example, the url is like "", where I need to populate this url in a cell with Excel formula. I've been trying some VBA code found on Google, but couldn't get it work.

My VBA script:

Sub query()
Dim row As Integer
Dim val As String
row = 1
val = Cells(row, 1).Value ActiveWorkbook.Worksheets.Add With ActiveSheet.QueryTables.Add(Connection:= "URL;" & val, Destination:=Range("$A$1")) End With
End Sub

• I don't want this macro to create a worksheet every time when executing it. I want to set the destination:=Range("Sheet1!A1"), but it seems to be wrong syntax.

1 Answer

If you don't want to keep creating new worksheets, take out the command that does it!

ActiveWorkbook.Worksheets.Add

You are also adding a new query each time you run this code. It would be better to assign a name to the query and then simply update it each time you need to change the URL.

The easiest way is to insert a web query manually to the desired location and give it a name. Lets assume you called it myquery.

Instead of ActiveSheet.QueryTables.Add, something along these lines:

 Dim mytable As QueryTable ' If you dont set the name, it will take ?date=20110716 as a ' name when it is added for the first time Set mytable = ActiveSheet.QueryTables("myquery") ' Update the connections URL mytable.Connection = "URL;" & ActiveSheet.Cells(1, 1).Text ' Update the request - it will return the new data to the spreadsheet mytable.Refresh

You cannot assume that there will only be one query in a spreadsheet as they are so easily added. So you must reference the correct query.