Menu
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?
400
Edit a query from a worksheet In Excel, select Data > Queries & Connections, and then select the Queries tab. In the list of queries, locate the query, right click the query, and then select Edit.
Query parameters are a defined set of parameters attached to the end of a url. They are extensions of the URL that are used to help define specific content or actions based on the data being passed.
From the Menu Bar (not the Ribbon), use the Data menu. Choose Get External Data > Run Web Query. (Older versions of Excel say Run Saved Query). The Choose a Query dialog displays defaulting to the Queries folder.
Go to Data>From Web to enter your URL, click OK, then select Basic to enter your login credentials to have a check. For more information, you could refer to the Other Sources :Web section of Import data from external data sources.
With ActiveSheet.QueryTables(1)
.Connection = "URL;" & NewURL
.Refresh
End With
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
