When you’re TM1-to-the-bone and somebody says “and then we need to send these reports out” , you think ”Well, that’s easy, it’s just an Active Form and a bit of vbs to convert TM1 formulas to values, pack and send”. And it’s all nice and straight-forward journey up to the point when you hit refresh in an AF and an ”Cannot Empty Clipboard” message box pops-up. No worries if you’re refreshing something manually, just click ok and off it goes, but it’s a complete show stopper for any automated updating script (triggered by TI’s in my case). Really interested whether somebody saw the same error.

I tried all possible Excel versions (2003, 2007, 2010) and got this error occasionally (refreshing 2 AFs at the same time would cause it almost certainly). TM1 was fixed at 9.5.2 FP2, couldn’t upgrade, so it might be solved in higher versions of add-in.

Converting TM1 formulas to values is well described on tm1forums and bihints, but I’ve found that traditional “run around, look into each cell formula and store it’s value if it’s a DBRW or anything else TM1-related” is terribly slow in big excel reports and switched to “paste-whole-sheet-as-values” (processing time dropped from 10 minutes per report to 20 seconds).

Paste as values approach contains a nasty nuance (did I tell you I “love” Excel already?): TM1 loves to store “almost-zero” values with very high precision (Enterprise Planning loves to do same stuff in publish containers, you end up rounding it to some meaningful 6-7 digits), if you ever saw a negative zero — that’s it. Negative zeros pasted as values cause ”A potential data corruption” error when opening a worksheet. Setting up precision to as displayed helps.

Back to clipboard errors: they appear when refreshing data from TM1 and even switching from TM1REFRESH (that refreshes all worksheets in workbooks) to per-worksheet refreshes (there’s an undocumented function for it) doesn’t make them go away. IBM recommends cleaning clipboard before refreshing, but it’s a useless approach, user32.EmptyClipboard empties system clipboard that has nothing to do with Office Clipboard that causes this error.

By the end of the day, I just wrote an overseer script that runs the script updating Excel report, grabs Process Identifier (PID) of started Excel process and monitors it’s completion in given timeframe. If there’s a clipboard error, Excel “hangs” and is restarted after timeout. Tested with lots of parallel updates and was amazed with the cruel “clipboard-contention” scenarios. But that’s really a sledgehammer solution and I don’t like going to OS level programming (PIDs, reliable inter script communications and etc) for such a menial task.

PS: Honestly, use Cognos BI bursting functionality, it’s way easier )

Update 23/01/2013: Looks like Clipboard errors are mitigated / fixed in 9.5.2 FP3