When I ended up getting this error, the first thing I checked was whether there are any hidden instances of EXCEL and there were none!!!
I was thinking that probably “Reduce Excel File size” option was causing the problem (not sure why I thought that way, maybe because it was not reducing the file size), so I disabled it and went ahead with a refresh.
I was under the impression that I’ll get the same error but to my surprise the refresh went fine!!!
Now I wanted to prove that it is because of “Reduce Excel file size”. I enabled the option, performed a refresh believing that I’ll get the error and I could file a bug (or even curse people).
Nope again no errors!!!! This was a forgotten story and then yesterday a colleague of mine called up and asked “Have you seen this error?” (You should have seen the smile on my face).
He was going to re-create the sheet and I advised him not to, “Just perform a refresh”. (with a heavy tone) (He might have thought this guy is crazy, didn’t say anything and went with the refresh).
He didn’t get the error and his refresh returned data from Essbase.
Back of my mind I wanted him to get the error and prove that it was because of “Reduce Excel file size”. But it seems like that is not the reason.
If you are getting this error, wait for some time (or even disconnect your connections), and perform a refresh.
I’m listing out possible solutions for the error here.
- Check whether you’ve a correct version of Smart View/ EXCEL Add-in
- Check for hidden EXCEL instances, close them and perform a refresh.
- Perform a refresh and see whether it solves the issue.
- Check whether you’ve any EXCEL formulas that is returning strings.
- If there is another solution please post it here. š
HTH
I also faced this few days back and it was so frustrating…what i did is copied all my content into a new excel and deleted the old excel and saved this new excel with a bit different name than the previous one and then again did refresh and it worked..but if you save it with the same name if gives the same error. So not sure what it looks for this solved my issue.
one more solution
The issue is solved by the following workaround:
Uncheck 'Preserve Formulas and Comments on Adhoc operation (except pivot)' on the member option table. The replacement field is kept as #NumericZero.
Save the workbook (after deleting the extraneous rows).
I found that the error was caused by thousands of blank rows being wrongly included in the Smart View grid.
I ran this VB:
Sub Last_Cell()
ActiveSheet.UsedRange.SpecialCells(xlLastCell).Activate
End Sub
which goes to the last active cell of the sheet. Use this to delete the unneeded rows and refresh. No need to save as a new workbook.
That was it!!! I Deleted all the rows under what i thought was my grid and then it worked. Thanks
i was getting the message because the Essbase table extended to row 1800 of a spreadsheet that only should have had about 77 rows. I did a CTRL-End, highlighted all rows from there to the last row of data (Row 77), and deleted the entire rows. Refreshed with no problem.
I thought I did update this one on the post, I saw this like an year and half back. Thanks for updating Shawn M
If I remember correctly, you can get this error when you have more than 65,535 contiguous empty cells.
Thanks, Tim. That’s an interesting one.