tim laqua dot com Thoughts and Code from Tim Laqua


Excel 2007 Hangs When Trying to Edit an OLAP Cube Filter

Ok, it doesn't hang every time you try to edit an OLAP cube filter, but sometimes - it appears to. In reality, I've never seen it permanently hang - just kind of go away for a while. Here's the basic symptom that the business will report to you:

"Excel freezes (or hangs) when I try to change this filter"

Most of us have seen this at one point or another and shrugged it off as a busy time or processing is going on or there are cats clogging up the tubes, etc. Tonight, I finally decided to figure out what's causing it.


Changing Server Names in Excel 2007 Embedded Workbook Connections

Ok, so you want to migrate servers, eh? Well - all your business users pry have a couple thousand Pivot Reports lying around that point to the old server. We need to point all references to the old server at the new server.

There are two places these references exist (afaik) - The My Data Sources folder (Windows XP / Excel 2007) which holds all of the odc objects for the logged in user, and the real pain - Embedded data sources in Excel Workbooks.

For the odc objects - you can just loop through the My Data Sources folder files and do a little find/replace action.

For the Excel files - we need to first find all the files, then loop through them and hunt for embedded Connections. Once we find a connection, we can just blindly replace occurrences of the old server name with the new server name. The first thing we need here is a function we can call recursively to go hunt down all the excel files - then we loop through and play with the connections (when found).