Unfortunately, sometimes the XLF file gets corrupted, and when this happens, it is obviously never at a good time. The problem is that it is not exactly clear why and when this happens, so it is hard to prevent this from happening at all. The only thing we know is that the more components and Excel logic used in a dashboard model, the higher the chance is of this happening. Also, using MS Excel and SAP BusinessObjects Dashboards at the same time can cause trouble.
When an XLF file is corrupt, the following error message is given when the file is loading or when you are working on it:
Older versions of SAP BusinessObjects Dashboards (Xcelsius) show the following message: An error occurred while communicating with Microsoft Excel. To prevent further data corruption, Xcelsius must close. After clicking the OK button, SAP BusinessObjects Dashboards exits.
A best practice is to create a lot of (local) backups of the dashboard you are working on. Try to save a copy for every 30 or 60 minutes of work, or after making a bunch of changes. Do this consistently! You can use a simple incremental file name strategy to tell the files apart. For example, use the date and time of the backup at the end of the file name (dashboard_name_201411201556.xlf, dashboard_name_201411201623.xlf), or just use an incremental number with each backup file (dashboard_name_001.xlf, dashboard_name_002.xlf).
When a corruption error occurs, you can revert to the last working backup. If this doesn't work or isn't possible, you can try this recipe where we will try to replace the corrupt Excel file.
- Make a copy of the corrupted XLF file.
- Rename the extension (
.xlf) of the file as.zip. You will get a Windows message about changing the extension, which could cause the file to be unusable. Click Yes.
- Open the ZIP file. Here you will see a folder and two documents. In the folder, the images of the dashboard model are saved. If you don't use images, you won't see this folder. In the
document.xmlfile, the dashboard model properties are stored (component size, position, and so on). Thexldocfile is the spreadsheet model.
- Drag the
xldocfile out of the ZIP file. - Rename the
xldocfile asxldoc.xls. - Open
xldoc.xlsin MS Excel. As you can see in the following screenshot, there are a lot of #VALUE errors in the spreadsheet file.
- Save the file as an Excel Workbook file (
xldoc.xlsx). Close MS Excel. - Now reopen MS Excel and load the XLSX file you just created. Save it as an Excel 97-2003 Workbook file (
xldoc.xls). - Rename
xldoc.xlsback toxldoc. - Go back to the ZIP file and delete the
xldocfile from it. - Drag your edited
xldocfile into the ZIP file. - Rename the
ZIPfile back toXLF. - Open the dashboard model in SAP BusinessObjects Dashboards.
You might need to repair some cells in the spreadsheet, but at least you can access your dashboard model again.
Corrupt files are a big frustration among SAP BusinessObjects Dashboards developers. This unofficial workaround might solve the issue by hacking into the XLF file and replacing the spreadsheet source file. Use this method only when necessary.