Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How to create a Web Query in Excel 2010 with the connection string (url) from a cell? For example, the url is like "http://www.xxx.com?date=20110716", 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.
share|improve this question
1  
Learn C# and build a Com Addin Library. – KronoS Jul 16 '12 at 6:41
    
Show us the VBA code, I might be able to give it a go. Generally I agree with @KronoS and would do it in a C# VSTO add-In myself. – Jeremy Thompson Jul 16 '12 at 7:38
    
@JeremyThompson, I just added my VBA code, please have a look. – Stan Jul 16 '12 at 7:49
up vote 1 down vote accepted

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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .