Repaired Part: /xl/worksheet/sheet2.xml part.

[craig159753]

Hi all, I have an EXCEL workbook which is created by running some VBA. Recently when I create the file it sometimes generates perfectly but once I save, close and go to re-open I am told the file is corrupt and needs to be repaired, the message upon opening states

"EXCEL was able to open the file by repairing or removing the unreadable content.​

Repaired Part: /xl/worksheet/sheet2.xml part."​

The file does get repaired and I can see from the repaired log which sheet is the problem sheet i.e. Sheet2. After sheet2 is "repaired" sheet2 has lost the custom widths, conditional formats, frozen cells and data filter option.

Now I want to resolve this problem. I have managed to change the file name of the corrupt file to have a suffix of .ZIP and extracted the Sheet2.xml file. Then using the repaired file done the same, i.e. extracted the repaired Sheet2.xml file. I tried to compare to see if I can see the problem code and no luck.

I don't have any XML editors to use, is there anyway to figure this out? I would attached the file but cannot see an option, I would paste the code but it is rather large

Thanks
Craig

 

[RoryA]

Can you give us an idea of what the code does to the file? Does it add any data validation, or do any custom sorting? Also, what conditional formatting does it add?

This sort of problem is usually down to the code doing something that isn't supported.

 

[craig159753]

Can you give us an idea of what the code does to the file? Does it add any data validation, or do any custom sorting? Also, what conditional formatting does it add?

This sort of problem is usually down to the code doing something that isn't supported.

Hi Rory,

Yes several cells have data validation, which are just lists, like "A", "B", "C", etc. The conditional format is very basic, highlights a cell red when it is missing. One weird thing which is happening it seems to be dependent on the number of rows added to sheet2 by my VBA process, if says I add 4 rows no problem but when I add say 12 it doesn't like it.

Thanks
Craig

 

[RoryA]

Is the data validation added by the code? If it is, and you use a comma separated list of values, you will get an error on reopening the file if the length of that string exceeds about 255 characters.

 

[craig159753]

Is the data validation added by the code? If it is, and you use a comma separated list of values, you will get an error on reopening the file if the length of that string exceeds about 255 characters.

Hi Rory,

This is spot on! The data validation is added using VBA code. It gathers the values from one column and creates a comma delimited string. I.e. the more rows which were added the larger the data validation grew, and therefore sometimes exceeding 255 characters.

Domains
A
B
C
D
G
H

Data validation = "A, B, C, D, G, H"

Solution: I turned off code which assigned the data validation now as it isn't needed anymore.

Thanks again for your help!
Craig

 

[RoryA]

Glad to help. I love it when a shot in the dark pays off!!