Subscribe in a readerExcel File Remediation Tool
Many companies have been using spreadsheets for decades. Sometimes spreadsheet models may go back as much as 20 years, when the first steps were taken to build them, sometimes using Lotus 123 or other spreadsheet applications.
More often than not, these (often business critical) models have been extended considerably over the years:
- Sheets have been inserted, or copied over from other workbooks
- Formatting has been changed hundreds of times to thousands of cells
- Defined names (named ranges) have been inserted. Knowingly or inadvertently (e.g. by copying worksheets)
- Corrupt defined names have remained from merge from other spreadsheet file formats to Excel
- User defined functions in VBA
- .........
My customer (who had already been in contact with Microsoft premium support about these issues without getting resolutions) asked me whether it would be possible to build a wizard-like tool to salvage this kind of files. Even better: to remove possible corruptions entirely, without damaging the integrity of the spreadsheet model, without removing formatting and also making sure any VBA code is retained.
The tool I built comes with the menu shown below.
These screenshots show the steps of the wizard.
First Step: file selection.
Next step: what actions must be performed:
Last step: What statistics must be gathered:
Since remediation may be a lengthy process, the tools shows its progress. Note that the progress indicator has a stop button, with which the process may be exited.
My customer has repeatedly confirmed this tool has proven to be an invaluable time-saver, which has enabled them to salvage business critical files, they otherwise had to consider as being lost.
Comments
All comments about this page:
Comment by: Andrew (12/3/2009 9:04:05 AM)Hi,
I'm very interested in your remediation tool. I am currently using fastexcel to clean rubbish out of the sheets but it is very time consuming, the automated features of your remediation tool look interesting. In fastexcel I use the clean workbook feature and the name manager on idividual files, can you improve on this?
I also use another piece of software that checks for VBA issues between the old version (2002) we were using and 2007, I'm not very happy with this so will be interested if you have anything to help there as well.
I need a safe utility which can ideally work on bulk files as I am fixing files for a few hundred people during the migration.
The name manager is a great tool but I'm having a problem when there are too many names, if I have a sheet with 1400 names then it locks up even though it works fine when I delete 3/4 first. Is there a clever way to use it to cope with the problem?
Thanks,
Andrew
Comment by: Jan Karel Pieterse (12/3/2009 10:35:51 AM)Hi Andrew,
Your email bounced, could you please get in touch using the email link below?
Comment by: Joseph (12/8/2009 5:01:00 PM)How do I purchase the Excel File Remediation Tool? I don't see a BUY button.
Comment by: Jan Karel Pieterse (12/9/2009 1:48:33 AM)Hi Joseph,
There is no buy button (yet). I've sent you an email.
Comment by: Sherwin Chase (4/9/2010 5:51:03 AM)I use and appreciate the functionality of your NameManager and the licensed version of RefTreeAnalyzer products. I am interested in getting more information on pricing and availability of your Excel File Remediation tool. Is it likely that the release of Office 2010 will have a significant effect on the functionality of the tool?
Comment by: Jan Karel Pieterse (4/9/2010 6:07:47 AM)Hi Sherwin,
I expect it will work just fine in Excel 2010.
The tool comes in two versions:
- A limited functionality version, which I call the "User" version.
- A fully functional version, which I call the "Support" version.
Send me an email using the address at the bottom of this page if you want more information.
Comment by: Luoyong (4/17/2010 11:27:36 AM)Dear Jan:
after a long time work with my VBA project, I save the file and shut down my computer. when I turn it on and begin to continue work with My VBA project. I can't open the file normally, the excel told information" lost Visual Basic project", then the auto-recover function works automatically, but all the VBA code was disappear.
could you give one hand! how can I find my lost VBA Project.
thank you!
Comment by: Jan Karel Pieterse (4/17/2010 11:42:11 AM)Hi,
Depends on the file format you used.Is it an xlsm file?
Have a question, comment or suggestion? Then please use this form.
If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.