changing a URL for Excel Web Query

Question

I have an Excel 2010 spreadsheet that gets information from a data connection. On the properties of the connection is the "connection string" which is a URL with several parameters that are passed to the server in the query string. If you click "edit query" you can change the URL and then import new data based on the new URL. I need to do this via VBA.

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

How can this be done?

Answer 1

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