How to fix 'Workbook that you selected cannot be opened' error

Excel services work with Excel 2007 and allows you to share, load, calculate Excel workbooks on Microsoft SharePoint Server 2007 portals and dashboards. You can share an entire Workbook or just few parts of it like charts, tables etc. By using a Web browser, end users can view and interact with the workbooks. However, at times, when you publish a workbook to Excel Services and try to load it in a browser, you might run into an error message. Such situations erupt when you try to load a corrupt workbook. You can use a healthy backup copy of the Excel file and upload it again to the server. However, if you cannot access a backup copy or if it is not an updated one, then you must consider using an Excel repair software.

Consider a scenario, wherein, you have created an Excel 2007 spreadsheet containing information about the sales revenue of your company. You have an online meeting with your colleagues where you have to discuss the figures and stats given in the Excel sheet. You save the file in .xlsx format and publish it to Excel Services. When you use a Web browser to load the particular Excel workbook, Excel Services doesn't load it. Additionally, you receive the following error message:

"Unable to Load Workbook

The workbook that you selected cannot be opened.
The workbook may be in an unsupported file format, or it may be corrupt.
Would you like to try and open this file in Excel?"

Cause

The error message can be received in the following situations:

• If the workbook contains a formula which has a space character in front of the (^) caret operator.
• Or if the workbook in question is corrupt or damaged

Resolution

The following Repair excel file steps must be considered to resolve the problem:

• You must do away with the space character which is placed in front of the caret operator in the formula
• You can also save the affected workbook in non-XML binary format (.xlsb) and then try to republish it to Excel Services
• If the problem doesn't get resolved, then it is for sure that the workbook is corrupt. You must employ a third-party Excel repair application and fix the errors in an easy way.

How to address workbook corruption in Excel 2007

Corruption of Excel workbooks is not a new phenomenon. Irrespective of the version of Excel, a workbook is quite prone to getting damaged in many instances including improper shutdown of application, power fluctuations or abrupt system shutdown. Despite incorporating advanced features, Excel 2007 can annoy users with corruption problems. What to be done? If you have an Excel 2007 workbook and you are encountering problems while opening it, then it is quite evident that it is corrupt. In such scenarios, you can obviously turn up to a backup copy provided if it is updated and in a clean state. However, if you cannot bank upon a backup copy, do not think you have lost valuable data forever. Many Excel repair tools have been launched lately which promise to repair damaged or corrupt Excel files without modifying the original data.

Think of a situation, wherein, you have an Excel 2007 worksheet containing contact and personal information of your team members. One fine day, when you try and open it, you might not be able to access its contents. In addition to this, you might receive the following error message:

"Excel found unreadable content in Book_Name. Do you want to recover the contents of this workbook."

After clicking Yes to the error message, you receive the following error message report:

Removed Records: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml part (PivotTable cache

Cause

The above Excel error could erupt in the following conditions:

• Presence of a PivotTable in the workbook which uses key performance indicators (KPIs)
• If the KPIs are made in the Analysis Services Business Intelligence Development Studio
• If one or more KPIs have an expression in the Current Time Member property
• Lastly, the error might also surface if the Excel 2007 workbook is corrupt

Resolution

Try the following workarounds which would assist you in executing Excel file repair:

• The issue can be resolved if you take out expressions from all Current Time Member properties
Being an optional property, it can be done away with to get rid off the error message.
• If the above solution doesn't really work, then you must have to employ effective Excel recovery strategies to fix corruption

Excel recovery solutions when Workbook is Damaged

Excel is one of the most commonly used spreadsheet application, owing to its ease of use and the a large number of features, including arithmetic operations, graphing tools, pivot tables, and more. Despite extending all these features, it has not been able to escape a few drawbacks and bugs. Amongst many, one is damage to workbooks, due to power surge, human error, virus attack, abrupt shutdown of the system, file becoming too large, incompatible upgradation, application malfunction, network issues, improper coding etc. In such case, one may try to repair Excel file, manually or using Excel file repair tools.


If one fine day, a person opens an Excel file to make changes in it, and finds that is is damaged, he/she is in a state of shock. When that initial stage of shock passes, it is only then that a person can think rationally. And, then it is recommended to think of that backup, which was created when work had just been completed in that file. But what to do if the backup doesn't work, or worse you forgot to created a backup.


In case a file is damaged, it may either not open, or may not show data in correct form (may be garbled text) after opening. To resolve this problem, and extract your data, follow the directions given underneath:


If the file doesn't open: Try to open your workbook with the 'Open and Repair' feature. When we select Open from the File menu of Excel application, then the dialog box which opens allows you to select the file which has to be opened, after which you need to click on the small arrow on the right side of the 'Open' button. Thereafter, you should select 'Open and Repair'. Following this, you need to click on 'Repair' when prompted.


If the file opens:

You may be able to filter it, by saving it as HTML Web Page, or XML Spreadsheet format. After this, you should close the file, and then reopen, and save it as Microsoft Excel Workbook. You may not be able to save everything, but most of the things.


If neither works, then you need to opt for a third-party Excel file repair tool. These tools apply complex algorithms to repair Excel file and extract maximum data from it.