A friend that works with Excel Services a lot and moves a ton of files around from development environment to production reached out and asked me to put together a tool that could update embedded Office Data Connection properties. Here it is: XLODCTool.
I don’t have time for a complete read me file right now, so here is first pass at “how to” instructions:
Setup trusted Excel file library to use both odc and embedded connections in your SharePoint. Now Excel Services will first use embedded in the file connection string unless you have “Always use connection file” check box checked.
XLODCTool will allow you to swap various connection string parameters in bulk in all selected files from a trusted Excel files library or the file system. If you have WebDAV on open your trusted doc lib in explorer, drag and drop Excel files with ODCs on the grid and then:
Step 1: Fill out ODC parameter name exactly as it appears in the connection string. Most likely it will be: “data source”.
Step 2. Fill out From value if you want to replace specific parameter value or leave it blank to set all occurrences of the parameter set in Step 1 to new value. Fill out To value (To can’t be left blank). E.g., if you supply value of “dev.box.com” in From box and set To box to “test.box.com” you will change: “data source=dev.box.com” to “data source=test.box.com” only in embedded connections where “data source= dev.box.com”. If From is left blank then all connections will be updated to “data source=test.box.com”.
Step 3. Click Process Files button and watch the progress.
NOTE: I’m skipping all connections that contain Extended Properties for now. Didn’t have much use for it yet, but will add it if there are users that need it, so leave a comment if you want it.