Switching Data Sources in Excel 2007

re: Switching Data Sources in Excel 2007

Harlan Grove — Fri, 14 Dec 2007 09:03:14 GMT

So could one also switch between different servers simply by overwriting a file with the expected filename? For example, if the workbooks use a connection file named connectionfile.odc, create alternative connection files with additional characters in their filenames, e.g.,

connectionfile.server1.odc

connectionfile.server2.odc

and simply copy one or the other as connectionfile.odc, thus overwriting it if it already exists.

If so, simple enough to handle outside Excel before launching Excel. But it'd also introduce the possibility of altering connection files to point to data sources containing malicious code.

Even with all of this, much less satisfying than using the old SQL.REQUEST function in which the 1st argument could be a dynamic expression which could change during the course of recalculation. But it's been clear for a while that Excel is evolving towards an overly resource-hungry grid control.

I would love to hear more about this …

Alexios — Sat, 15 Dec 2007 18:42:39 GMT

I would love to hear more about this …

<a href=http://anitarockey.00bp.com/map_959353972.html >Top Sites</a>

[url=http://anitarockey.00bp.com/map_959353972.html] Top Sites [/url][url=http://selenastevens.freehostplace.com/map_360100477.html] Cool Links [/url][url=http://members.lycos.co.uk/gradytoppah/map_700571099.html] Interesting Pages [/url][url=http://cherihayhurst.web2gb.com/map_687263434.html] Cool Links [/url]

re: Switching Data Sources in Excel 2007

Howie Dickerman — Tue, 18 Dec 2007 03:55:57 GMT

Yes - you can switch between servers by overwriting the connection file, but this will only affect workbooks where the checkbox to "Always Use Connection File" has been checked. Other workbooks won't be affected.

Good practice would suggest that you only want to do this when the connection file lives in a secure location and is only to be updated/overwritten by persons who understand how their change will impact existing workbooks in an organization.

This approach can work very well with a Data Connection Library on a SharePoint server that's appropriately locked down. In this instance an IT department can effectively move a set of workbooks from using one data source to another.

Hope this helps,

-Howie