When working with Excel Services these weeks, I’ve come to the conclusion that the error messages are worse than vague, they are completely undocumented (as far as I’ve found) and no-one has blogged about these errors. Time to change that.
When trying to set a range using the SetRangeA1 method:
Unable to perform the operation. The operation refers to items that may not exist in the workbook, may not have been marked as viewable by the workbook author, or the workbook cannot be opened in the browser.
Locked workbook syndrome
This error could indeed refer to the fact that you are trying to set a range with e.g. merged cells and thus not rectangular or that you didn’t publish some of the cells (which by default are all published) and you probably already know that it needs the ability to open in the browser.
However, have you considered the fact that it may be checked out to someone else? Hm, maybe even in that case Excel Services will notify you, but what about if the library the Excel file is in, doesn’t require check-out? When you click “Enable editing” in Excel client, the lock to the file isn’t always freed when exiting the Excel client.
The solution? Check out the file and check it back in (with no changes). Hope this helps someone out there.
Funny enough, invoking SetRangeA1 and CalculateRangeA1 work perfectly. Just getting data is problematic…
Unset cells issue
Another possible problem is that the cells you are trying to read from were never before set to values in the workbook.
If you are for example trying to set a range in a worksheet that never had data in it before and you don’t save the workbook, Excel Services gives the exact same error when reading the cells’ contents.
The solution to this problem is to initialize all the cells with a value and clear them (so they don’t contain bogus values).
Edit: finally, after hours of looking and some shoulder-overlooking by colleagues, I’ve found the issue.
The hidden read
You can’t read ranges that are on invisible sheets. You can write to them, but you can’t read from them.
I really don’t get why, but that’s just it. It works when I unhide the sheet that I want to read from.
Absolutely bizarre, should be documented somewhere…
I hope this helps other developers struggling with Excel services!