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?

Connection strings and URLs are completely different things. — isekaijin
– isekaijin, Commented May 24, 2011 at 18:44
Uhm, not in this context they are not. The connection string is the URL that will be used to conenct to the server. Look at the wording yourself in Excel. — Jimmy D
– Jimmy D, Commented May 24, 2011 at 18:51

Tim Williams · Accepted Answer · 2011-05-24 20:41:13Z

6

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

answered May 24, 2011 at 20:41

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jimmy D Over a year ago

Awesome, thank you. Followup questions: Can I refer to the connection in any other ways instead of ActiveSheet.QueryTables(1)? Maybe by name for example? And lastly, after I do the refresh I want to save the workbook so the user won't be prompted to do so when they close it but if I try ThisWorkbook.Save after your With block I get a message saying that the Save will cancel the pending Refresh. I guess it's trying to save before the refresh finishes. Is there a way I can handle this?

Tim Williams Over a year ago

I would suggest you try recording a macro while creating a new web query. The recorded code will show you the options available: I think "Name" and "BackgroundQuery" are the properties you're interested in. If you set BackgroundQuery to False then it should finish updating before your code moves on to the Save operation.

Jimmy D Over a year ago

Thanks @Tim. I just found the Backgroundquery:=false property and it works great.

xdumaine Over a year ago

I always get "Subscript out of range" when I do this. Any ideas?

Tim Williams Over a year ago

@roviuser - I would guess there's no QueryTable on the active sheet.

|

Collectives™ on Stack Overflow

changing a URL for Excel Web Query

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related