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.