Excel 2010 Unreadable content

I have a macro-enabled workbook originally prepared in Excel 2003, migrated to Excel 2010 some two years ago. After migration this workbook would give me the message on open that there was unreadable content and would I like Excel to repair. I always did and never lost any data. The repair affected some conditional formatting which I never identified. In what I thought would be the end I removed all conditional formatting from the workbook and deleted the sheet on which the error occurred. But the messages are coming back now with regularity. After repair I get this message,
Removed Feature: Data validation from /xl/worksheets/sheet5.xml part
I figure this as interesting because there is no sheet5 in my workbook. All sheets are code-named differently. As for data validation there is a peculiarity on most of my sheets (there are about 25 of them) in that the code builds a validation drop-down when a cell is selected and deletes it on the next selection_change. Anyway, I looked at the log and found this,
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error044160_01.xml</logFileName><summary>Errors were detected in file 'D:\My Documents\ACCOUNTS\Equity Trading SGX.xlsm'</summary><removedFeatures summary="Following is a list of removed features:"><removedFeature>Removed Feature: Data validation from /xl/worksheets/sheet5.xml part</removedFeature></removedFeatures></recoveryLog>
Here, I wonder who might be behind schemas.openxmlformats.org. Is this related to OpenOffice? I am running a licensed MS product and never had OpenOffice installed on this computer.

Now I wonder if this might be related to my long-standing feud with Office 2010 which has prompted me to ask several times though a solution was never found. Basically, my Office Installation (affects Word as well as Excel, and probably the others which I hardly ever use) doesn't recognize files with Office extensions. Somewhere, deep in the registry XLS (or DOC) files are associated with Excel 2010. When I double-click on such files the associated application is loaded, the file opens, and it is added to both Recent lists (the one shown from within Excel and the other shown next to the Excel program accessible from the Start button).
None of this normal behaviour is available for XLSX or XLSM (of their Word equivalents) files. To wit, double-click does not open any application. If Excel is already running, it doesn't load the workbook. If I open the file from within Excel it will be added to the Recent list within Excel but not the one in the Start menu.

Here are the previous threads:-
Can't open Workbook on double-click
Sort out various Excel versions on one PC
At this time my PC has only one version of Office installed. I am still interested in opening files on double-click though I have largely given up hope. However, the thrust of today's question is whether or not the error I am now facing is related.
LVL 14
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle GreenCommented:
I Googled your issue because I've never received that particular error before, or at least not more than once. One concensus I found was that your file may have been corrupted, and the recommendation for that was this tool: http://www.excel.fixtoolboxx.com/

I also found another thread on EE: https://secure.experts-exchange.com/questionUpgrade.jsp?qid=26378748&redirect=/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26378748.html

Other than that I would probably have to look at your file. If it contains data sensitive to your company let me know and I'll post a disposable email address as I do not see a messaging tool on this website.
FaustulusAuthor Commented:
Thank you for your quick response. I got the FixToolBox to look at the file but it didn't tell me anything without paying for the program. Apparently, it was able to read the XML data correctly. I couldn't read the other thread (funny: the page opens to tell me that I have unlimited access to all solutions - and please subscribe before you can use it) but I don't expect anything there that I don't already know. This kind of error was quite common when Excel 2007 came to the market and people were upgrading from XL 2003, and while everybody knew about it no one seems to have known how to deal with it.
I wouldn't have a problem giving you a sanitized version of my workbook but don't think it will be useful. You see, the error doesn't occur every time. It seems dependent upon some other action having been taken. There is a lot of automation, like the creation of validation lists on the fly, and unless a pattern can be detected one really doesn't know where to start looking. In the end it may be that the error disappeared because of the santiation, or the computer is defect, or the Excel installation isn't what it should be.
In fact, that is the point at which I am trying to look for a recurring pattern. If a link could be established between my faulty installation and this error perhaps a pointer could be developed from that toward where the installation has its fault.
Kyle GreenCommented:
If you feel you have a faulty installation try this... First, open cmd as Administrator and run
sfc /scannow

Open in new window

which will verify the integrity of the system files. Probably not at all necessary here but it won't hurt for what we're doing. After that head over to the Uninstall a Program window and select Office 2010, Change, Repair. Your installation should be fine after that. 2010 is a very solid suite, more so than 2013 and it's installer/license management scheme. (Microsoft's Office development team made legendary-epic poor decisions with 2013)

After those steps I feel safe ruling out your installation. Does the spreadsheet have custom vb in it? Also just to recap, you have saved it in the most modern file format right?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

FaustulusAuthor Commented:
This looks like fun. Thank you for doing this for me!
I ran the scan with the expected result, "... Did not find any integrity violations."
The Repair is still running, but there won't be any message other than that everything is OK.
What's custom VB. every bit of code in it was written by me.
I don't pay attention to the version I save a workbook in, trusting Excel to do that for me. The workbook in question is in xlsm format, version 14, I believe. What else to look for? How to check?
So we wait for the next time the workbook doesn't load properly. Very likely, that won't happen within the next few days, but what should I do if it doesn't, or if it does?
Rory ArchibaldCommented:
If you get this error occasionally but it always relates to data validation and your code manipulates data validation on the fly, I would tend to suspect your code is the cause. Can you post it?
FaustulusAuthor Commented:
Thank you for your offer to look at my code, but I really don't want to do this to you, or any one else. There is a lot of code, and even the guilty looks innocent. There must be one particular action which causes Excel not to be able to record what the code instructs. No immediate error occurs, and the book saves and closes quietly, but next time it is opened the error floats to the top.
Since the error complains about validation, this is the code that sets and deletes validation. Perhaps my method of deletion is incomplete. As I said earlier, a cell receives a validation list when it is selected and the validation is removed when the selection changes.
Sub SetValidation(Cell As Range, _
                  Optional ByVal Del As Boolean)
    ' Set or delete validation in Cell
    Dim Lv As String
    With Cell.Validation
        If Del Then Exit Sub
        Lv = GetListValues(Cell.Parent)
        If Len(Lv) Then
            .Add Type:=xlValidateList, Formula1:=Lv
            .InCellDropdown = True
            .IgnoreBlank = True
            .ShowError = False
        End If
    End With
End Sub

Open in new window

My favorite suspect is the following code, however. It copies the row above the new row and pastes it into the row just inserted.
Function InsertRows(Ws As Worksheet, _
                    ByVal InsDate As Date, _
                    Optional ByVal InsRows As Long = 1) _
                    As Long
    Dim AtRow As Long
    Dim NewRows As Range
    Dim Rs As Long
    Dim C As Long
    C = DateColumn(Ws, True)
    AtRow = InsertionRow(CLng(InsDate), C, Ws)
    With Ws
        If AtRow <> (LastRow(C, Ws) + 1) Then _
           .Range(.Rows(AtRow), .Rows(AtRow + InsRows - 1)).Insert
        Set NewRows = Range(.Rows(AtRow), .Rows(AtRow + InsRows - 1))
        Rs = SourceRowNum(AtRow, Ws)
    End With
    With NewRows
        On Error Resume Next
        .Columns(C).Cells.Value = InsDate
        .Cells(C + 1).Select
    End With
    Application.CutCopyMode = False
    InsertRows = AtRow
End Function

Open in new window

This code is usually followed by action to restore formulas. Formulas are copied form a source row which is different from the one used for inserting the new row.
Sub RestoreFormulas(Ws As Worksheet, _
                    ByVal Rs As Long, _
                    ByVal NumRows As Long, _
                    ParamArray Cols())
    Dim Rt As Long
    Dim Sq As Nsq
    Dim i As Integer
    Sq = PostingSequence(Ws)   ' either top to bottom or bottom to top
    Rt = DateColumn(Ws, True)       ' for use in next line only
    If (Sq = NsqBottom And (Rs + NumRows - 1) = LastRow(Rt, Ws)) Or _
       (Sq = NsqTop And Rs = FirstDataRow(Ws)) Then Exit Sub

    For i = 0 To UBound(Cols)
        Rt = Rs + IIf(Sq = NsqTop, -1, NumRows)
        With Ws.Columns(Cols(i))
            .Cells(Rs).Copy Destination:=.Cells(Rt)
        End With
    Next i
    Application.CutCopyMode = False
End Sub

Open in new window

I have no particular reason for suspecting this code except that rows are inserted less frequently than other actions in the workbook. It just might be that the error crops up after a row has been inserted. All I can say with assurance is "not every time".
Suspicion is also attached to these procedures because they used to cause havoc with conditional formatting before I removed it. I am now wondering if an error might be created by some Selection_Change event firing during the insert/copy/paste action trying to set or delete a validation. Application events are turned off during the process, but sometimes they turn themselves on again at the end of a procedure.  - I feel this is all too far-fetched to even warrant checking.
Another action that might cause the problem could be manual deletion of a row. Recently, I have done some modifications. I inserted rows for testing purposes and then deleted them manually. If this is to be linked to validation. I would need to look at a sheet where cell validation is based upon a named range which is located at the bottom of the sheet. Therefore the range's address would change as rows are inserted or deleted.

Let us see, if we get lucky with the above. If not, I think we should drop the thread until we have some idea of what to suspect. Checking everything would be just too much work with too little hope for success.
Rory ArchibaldCommented:
My first guess would be that your problem is here:

        Lv = GetListValues(Cell.Parent)
        If Len(Lv) Then
            .Add Type:=xlValidateList, Formula1:=Lv

Open in new window

There is a limit to the length of string you can assign to a validation list, which is 255 characters. If your Lv string were longer that that I suspect you could get the sort of problem you describe.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FaustulusAuthor Commented:
That seems to be spot-on!
The list is slightly longer. It loads OK (into the validation drop-down) despite of that. So, would you say that the error occurs when such a list wasn't deleted (as might happen during testing, but less often during actual use)? Or would you say that the file might get corrupted even if the list is deleted before saving?
Rory ArchibaldCommented:
I would expect that as long as it is deleted before saving you would probably be OK but I couldn't guarantee it.
FaustulusAuthor Commented:
Thank you both for the effort you have put into this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.