Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.