Remediation utility

Excel 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 in two versions, a User version and a Support version. The menu is shown below.

Support
User

These screenshots show the steps of the wizard.
First Step: file selection.

Support
User

Next step: what actions must be performed:

Support
User

Last step: What statistics must be gathered:

Support
User	N.A.

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. The User tool will show some statistics when it is done.

Support
User

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

Showing last 8 comments of 16 in total (Show All Comments):

Comment by: Tom Huntford (12/17/2010 2:12:07 PM)

This may be just what I need. I have a business-critical Excel 2010 file that is getting slower and slower, even though the size stays at 5 meg. I tried saving it back to xls format, thinking maybe the complex conditional formatting ranges in 2010 were the culprit. I got a list of stuff in the "Significant loss of functionality" screen. One of the items is "formulas in this workbook are linked to other workbooks that are closed" - Location: "Defined Names". I had already removed all links and connections visible through the Data menu, so this was surprising. I looked through all named ranges and there are no cells with formulas referring to remote files. A colleague tried to open the file on a computer not connected to the network and it wouldn't open. We think there may be some kind of named range that we cannot find by normal means. Does this sound like something your product can help with?

Tom Huntford
MRP Administrator
Freeman Marine Equipment Inc.

Comment by: Jan Karel Pieterse (12/19/2010 12:14:42 PM)

Hi Tom,

Maybe downloading my Name manager helps you find the problem link?

www.jkp-ads.com//officemarketplacenm-en.asp

Comment by: Chad (2/27/2011 11:40:50 AM)

I have a workbook that somehow ended up with 6,900 defined names. I use about 40 of them. I tried name manager to delete external, error and hidden names but it has been thinking for the last 30 mins. Will remediation manager be a better tool for me?

Comment by: Jan Karel Pieterse (2/27/2011 11:35:54 PM)

Hi Chad,

Not necessarily. I would first try to delete the names step-by-step: first try to remove the error names, then the hidden ones. Make sure you do not remove Excel system manes, those are often hidden.
Then -finally- try removing the external names.

Comment by: Bobby Speake (8/11/2011 2:26:23 PM)

Where do I get Excel File Remediation Tool, I may be not seeing it but I need it

Comment by: Jan Karel Pieterse (8/22/2011 2:18:31 AM)

Hi Bobby,

Send me an email using the address at the bottom of this page.

Comment by: Jaden Glaukos (9/8/2011 1:01:12 AM)

Hi Jan,

First need to say thank you! For years I have enjoyed using the always
improving versions of Name Manager. I am fairly sure that at least
more than once keeping my job, or at least my self-respect, that Name
Manager made it possible to sift through issues in a name/formula
birds nest someone created...

Lately, I have been having issues brought to me (and that I have caused
myself) that are exactly like described above. Somehow the amount
of names, sheets and formats placed on a workbook over a period of
time corrupts them. I have looked for a solution for a while now and
it sounds like you recently developed one.

Are you going to retail this product, or have you already and somehow
I missed the link in the article?

All the best and cheers,
Jaden

Comment by: Jan Karel Pieterse (9/8/2011 1:02:06 AM)

Hi Jaden,

Yes, I do offer the product for purchase.
Let me know if you are interested by sending an email to the email address shown at the bottom of this page.

Regards,

Jan Karel Pieterse

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.