OK - I have found the answer to this issue, and here it is.

I setup an analysis services trace and attempted to connect to excel (pivot table)

This captured an error...

Hi Sam,

I have the same issue as you.

Win7 > Excel 2003 > cubes (SQL 2005 and 2008r2)

Did you ever get this working?

Thanks

Rob