Excel Services Compatibility Checker Addin - Beta
One of the feedback I hear most often is about just how hard it is to check and see if a file can load on Excel Services. On my most recent transatlantic flight (Israel->Washington) I decided to start writing an Addin that will allow people to both figure out what's wrong with their workbooks and fix them so that they work with Excel Services. You can download the setup from here.
Note: This tool is still in beta - I make no guarantees that it behaves as documented. Before messing around with your important files, I highly suggest you back them up to prevent any data loss.
Issues that the compatiblity checker knows how to find/fix
The following is a list of the issues that can be found:
AutoFix available (all of these will be removed from the workbook when auto-fixed):
- Comments
- DialogSheets
- Display Formulas
- Macro Sheet
- OleObjects
- Query Tables
- Shapes
- Validation
- XML Maps
AutoFix sometimes available (will be removed if possible):
- IRM - AutoFix will work if there's no password.
- Protection - AutoFix will work if there's no password.
- VBA - AutoFix will work if the trust-cente enables Automation to interact with the VBA project.
AutoFix will not work for these:
- Unsupported formulas such as External Workbooks and RTD()
- FileFormatResult
Once installed, the software gives you three options of processing files:
Excel 2007 Addin
After installing, Excel 2007 will have a Ribbon tab called "Excel Services". This tab contains three buttons that can be used to analyze and fix workbooks.
The first option (Server Compatibility Check) brings up a wizard that allows you to analyze the currently active workbook (the one that has the focus). The second option brings up a wizard that lets you fix multiple files with one fell swoop. The third button (Quick Fix) is a two-click automatic fix option for the workbook.
Server Compatibility Check
The "Server Compatibility Check" wizard presents you with a wizard that walks you through the process. After clicking "Next" on the Welcome screen, the workbook will get analyzed:
Once the analysis is complete, and if any issues were found, you will be presented with the list describing them:
As you can see, this workbook contains a slew of issues (it's my test workbook - it contains every possible issue Excel Services does not handle).
The user now has the option of viewing a full report (which will contain details about the problematic issues with the workbook) or continuing to the next stage:
The options presented are "AutoFix", "Manually fix the workbook" or "Don't do anything". There's also an option for creating a backup of the file.
Choosing AutoFix will attempt to fix all the found issues. Because some issues cannot be fixed automatically, it may end up landing you in the Manual Fix mode anyway. However, if all features were fixed, the wizard will notify you that the workbook should now be compatible with Excel Services and that you can start working with it.
The Manual Fix option brings up a Task Pane that helps you navigate the issues in the workbook:
As you can see, the manual fix task pane shows at the top a list of all the incompatible issues. Selecting comments, for example, will bring you to the first comment in the workbook, showing it to you. You now have the option of manually deleting it and moving to the next one or, instead, clicking the "Fix" button that will automatically fix it and move to the next one.
For some of the items, the system will also show you a short blurb about what the issue is and how to fix it manually.
Batch Check
You can also check multiple files instead of one by one. The "Batch Check" buttons brings up a wizard that allows you to select files (or a folder) and analyze/fix all the issues found there.
The first step is to choose if you want to AutoFix all the issues. If you choose to do that, you can also choose to create backups of the files before they are fixed. You can also choose to keep files that had problems with them opened. Next, you can choose where the files are brought from:
Entire folder will take a local folder and let you analyze/fix all Excel files in it. The second opton (Pick and Choose) lets you add files from various sources and process them.
Once a folder (or files) have been selected, the system will analyze and fix them.
Quick Check
The quick check button shows you a message box warning you that the workbook is about to be modified. It then processes the workbook and either lands you in Manual fix mode or tells you that the workbook is compatible with Excel services.
Excel Services Compatibility Checker - UI
Another executable that gets installed is the UI for the compaitiblity checker. When executing, it gives you the Batch Check wizard and lets you run it outside of Excel.
Excel Services Compatibility Checker - Command line
The last tool installed into the Program Files\ESCC directory is called "ESCC" and allows you to batch-convert files from the command line. Run ESCC /? for more information.
Known Issues
- In some cases, Excel will not exit properly when you are done with it because of the Addin. If you have simple reproes, I would like to know about them.
Summary
Hopefuly, this tool will make life easier for people wanting to adopt Excel Services.