Solved Help with File Repair message

January 10, 2018 at 00:55:53
Specs: Windows 10
Apologies for the length of this question. I'm trying to give all pertinent information so that you might be
able to help.

I have a workbook with 9 worksheets. One of the them is called "RD activity review log". Since adding a
new bit of code to the workbook, I keep getting a pop up message on opening, talking about the need to do a file repair. The first pop up says

"We found a problem with some content in "RD activity review log.xlsm". Do you want us to try 
and recover as much as we can? If you trust the source of this workbook, click Yes."

After I click Yes, and then enable editing and macros, I get a new pop up. The header says

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

The body of the pop up says

"Excel completed file level validation and repair. Some parts of this workbook may have been 
repaired or discarded.
Removed records: Sorting from /xl/worksheets/sheet2.xml part
Removed records: Sorting from /xl/worksheets/sheet9.xml part

You can then click on a file log to view more detail about the repair but I can't see anything that tells me
what the issue is.

I have two bits of code in the workbook. First there is a worksheet change event stored under sheet 3
"RD activity review log" which I assume is where the problem stems from. The code is:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim PasteSht As Worksheet
    Dim rng As Range
    Dim i As Integer
    Dim J As Integer
    Dim Temp
    Dim nams As Variant
    Dim F
    Dim Dex As Integer
    
    Set PasteSht = Worksheets("Paused Log")
    nams = Array("ID", "CI Owner", "Onhold date", "Area", "Issue", "Actions underway", _
    "Potential timesaving", "Estmated Delivery Date", "Priority", "Process", "Process owner", _
    "Submitter", "Process Overview", "Improvement idea", "Full progress update", "Next action date", _
    "Status", "Completion Summary", "Completion Date", "Additional Information", "To be discussed", _
    "High level overview")
    Set rng = Worksheets("Paused Log").Range("A1").CurrentRegion
    
    If Target.Column = 18 Then
      If Cells(Target.Row, "P") = "On hold" Then
        Target.EntireRow.Copy
        PasteSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        
    Sheets("Paused Log").Activate
      With ActiveSheet.Sort
       .SortFields.Add Key:=Range("A1"), Order:=xlAscending
       .SortFields.Add Key:=Range("R1"), Order:=xlAscending
       .SetRange Range("A1:V1000")
       .Header = xlYes
       .Apply
      End With
      
    
      For i = 1 To rng.Columns.Count
        For J = i To rng.Columns.Count
        For F = 0 To UBound(nams)
            If nams(F) = rng(J) Then Dex = F: Exit For
        Next F
        If F < i Then
            Temp = rng.Columns(i).Value
            rng(i).Resize(rng.Rows.Count) = rng.Columns(J).Value
            rng(J).Resize(rng.Rows.Count) = Temp
        End If
        Next J
        Next i
        ThisWorkbook.Worksheets("Paused Log").Cells.EntireColumn.AutoFit
        Worksheets("Paused Log").Range("D:V").EntireColumn.Delete
        
        

      End If
    End If

End Sub

The other bit of code is stored in the This Workbook module. It's a Workbook Before Save event. The code is

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim c As Range
    Dim J As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet

    Set Source = ActiveWorkbook.Worksheets("RD activity review log")
    Set Target = ActiveWorkbook.Worksheets("Overview")

    J = 2     ' Start copying to row 2 in target sheet
    For Each c In Source.Range("V1:V1000")   ' Do 1000 rows
        If c = "Yes" Then
           Source.Rows(c.Row).Copy Target.Rows(J)
           J = J + 1
        End If
    Next c
    
    'Delete unwanted columns for Overview sheet
    Worksheets("Overview").Range("H:V").EntireColumn.Delete
    
    'Sort Overview worksheet by Area column and delivery date in ascending order
    Worksheets("Overview").Activate
    With ActiveSheet.Sort
       .SortFields.Add Key:=Range("SortCol"), Order:=xlAscending
       .SortFields.Add Key:=Range("SortColDate"), Order:=xlAscending
       .SetRange Range("A1:G1000")
       .Header = xlYes
       .Apply
    End With
End Sub

Does anyone know why this would be? Or what I can do to solve the issue? The workbook performs as intended, so this is a nuisance rather than a critical error.

Many thanks


See More: Help with File Repair message

Report •

#1
January 10, 2018 at 08:28:14
✔ Best Answer
To start with, let me just say that without your actual workbook to test, it's kind of hard for us to know what the exact issue is. Since the code is not presenting any errors and "the workbook performs as intended", searching for syntax errors, etc. is probably a waste of time.

That said, I'll toss out a few things:

re:

Removed records: Sorting from /xl/worksheets/sheet2.xml part
Removed records: Sorting from /xl/worksheets/sheet9.xml part

Is there any chance that Sheet2 and Sheet9 are the sheets referenced by the Workbook_BeforeSave code? If so, that might point you towards that code as causing the problem. Perhaps that's a place to start. (More on that piece of code later)

The best I can offer is some suggestions related to how I would trouble shoot the issue if it were my workbook.

re: "Since adding a new bit of code to the workbook"

Well, that might also be a good place to start. Can you remove the "new bit of code" and see if the problem still exists? If the issue goes away, then you need to look very closely at your "new bit of code" to see why it is causing the issue.

re: "It's a Workbook Before Save event."

You might try disabling this code as a test or perhaps making some changes to the workbook but not saving them before you close the workbook. If the issue still exists even if the workbook isn't saved, then it's not that code.

In fact, you could disable all code and see if the problem still exists. If it still presents the error, then it is not VBA related. You might have a corrupt worksheet or corrupt data, etc.

Finding corruption can be a pain, especially if there is no apparent change once Excel has done it's own repair. Not knowing the structure of your workbook or the interactions between the various sheets, I don't know if it's possible for you to delete sheets (e.g. cut the workbook in half, then in half again, etc.) until the issue goes away. You might be able to narrow down the problem to a specific sheet or eventually even a range.

Bottom line is that you need to try and eliminate possible causes and test because the only time the issue arises is on opening. Eventually (hopefully?) you'll eliminate the broad cause of the issue. At that point you can dig deeper into the offending entity and (hopefully) find the root cause.

BTW...I don't recall if I've offered you this link before, or if you already know about these Debuggng techniques, but maybe this will help, either with this issue or in your general VBA life. ;-)

https://www.computing.net/howtos/sh...

Good luck!

message edited by DerbyDad03


Report •

#2
January 11, 2018 at 00:14:58
Thanks (again) DerbyDad03. You seem to have turned into my Excel counsellor! I'll start on the process of elimination. All the best.

Report •

#3
January 11, 2018 at 01:15:10
Hi DerbyDad03. It didn't take me long to track down that the problem is with this sorting code. I've changed the sorting code now and got rid of the problem.

message edited by ScottV


Report •
Related Solutions


Ask Question