On a recent project I taking part in I was authoring some Excel workbooks that were to be hosted in a SharePoint 2010 portal and display with Excel Services.
Our development machines were running Windows Server 2008 R2, as I’m sure many SharePoint development machines are. The gave a few issues when trying to use Excel together with ODC files hosted in the SharePoint portal.
Excel or any office app, for that matter won’t allow you to open files (workbooks or ODC-files) from an http-address on a standard Windows Server 2008 R2 installation, you will get an Error saying: “Could not open ‘http://yourserver'”. This is because the Desktop Experience feature isn’t installed per default.
So open Server Manager > Add Features > And Check “Desktop Experience” installing this requires a computer restart unfortunately.
The next issue you might run into, depending on how your development machine is configured is that you are unable to browse the SharePoint portal, in your open file dialog. Instead your office app tries you parse the site you open as text. This can be the cause of the WebDAV service not running. Open the services manager and start the WebClient service.