How do I change the TFS server in and Excel worksheet that's pulling data from a query

Question

Hello, I have an Excel spreadsheet that uses a TFS query to pull information on work items. I've created a lot of other formulas and charts based on this spreadsheet. My TFS server changed names and now the spreadsheet can't pull the information because the server no long exists. The query on the new server is the same, I just need to figure out how to point the spreadsheet to the new server. Does anybody know how to do this?

Answer 1

http://dicks-clicks.com/excel/ExternalData5.htm#ChangeConn

This shows some VBA to change the Connection property of the QueryTable object. It will work in any recent version of Excel. If you have Excel 2007, you can go to the Data tab and choose Connection - Properties to see the Connection string and SQL and you can change it manually there.

If you have 2003 or earlier and don't want to use VBA, you can download QueryManager

http://www.jkp-ads.com/downloadscript.asp?filename=QueryManager.zip

Which does pretty much the same thing as 2007 just in previous versions.

Oh, and I'm assuming querying a TFS is the same from Excel's point of view as any other server, but I've never done it personally. So this answer may be totally wrong.