Change URL for Excel Web Query

Question

I have an Excel 2010 spreadsheet that receives information from a data connection. The connection properties contain a “connection string”, which is a URL with several parameters that are passed to the server in the query string. If you click “edit request”, you can change the URL and then import the new data based on the new URL. I need to do this through VBA.

Let's say the connection string is currently http://myserver.com?name=foo I need to change this tohttp://myserver.com?name=bar

How can I do that?

Answer 1

With ActiveSheet.QueryTables(1)
    .Connection = "URL;" & NewURL
    .Refresh
End With