How to repair an excel file?

Question

I have an excel file which contains 4 worksheets. An outer system sends the excel files daily and my program reads then stores data to db. i have 3 files which may have been corrupted when my program tries to read the files it gives the error "External table is not in the expected format." when i try to open files in office interface it returns me Excel found ureadable content in the file.xlsx Do you want to recover the contents of this workbook? etc...

When i start program after i repaired the file with office excel and save again, it works. but i need to repair this files before program starts to read. Is there a way to repair excel files like office does?

I am using Microsoft.ACE.OLEDB.12.0;

Please don't just ask us to solve the problem for you. Show us how _you_ tried to solve the problem yourself, then show us _exactly_ what the result was, and tell us why you feel it didn't work. See "[What Have You Tried?](http://whathaveyoutried.com/)" for an excellent article that you _really need to read_. — John Saunders, Feb 18 '13 at 14:10
i am complete stranger to office operations like this i cant say i tried something on this situation. only thing i tried to open in ms excel and save again like this one [here](http://stackoverflow.com/questions/6608228/ssis-excel-connection-error-external-table-is-not-in-the-expected-format) — fealin, Feb 18 '13 at 14:38

score 9 · Accepted Answer · answered Feb 18 '13 at 15:11

You can use Excel Interop to open the file and repair as Excel does. But you can't use your program on a machine without MS Office. You may try third party libraries like:

The code for Excel Interop is as follows:

Missing missing = Missing.Value;
Application excel = new Application();
Workbook workbook = excel.Workbooks.Open(sourceFilePath,
    missing, missing, missing, missing, missing,
    missing, missing, missing, missing, missing,
    missing, missing, missing, XlCorruptLoad.xlRepairFile);
workbook.SaveAs(savedFile, XlFileFormat.xlWorkbookDefault,
    missing, missing, missing, missing,
    XlSaveAsAccessMode.xlExclusive, missing,
    missing, missing, missing, missing);
workbook.Close(true, missing, missing);

this is exactly what i am trying to do. – fealin Feb 18 '13 at 15:38 — fealin, Feb 18 '13 at 15:38

score 3 · Answer 2 · edited May 23 '17 at 12:30

3

As your file comes from an external source it may be blocked as a security precaution. The solution could be to unblock the Excel file programmatically like this:

public class FileUnblocker {
    [DllImport("kernel32", CharSet = CharSet.Unicode, SetLastError = true)]
    [return: MarshalAs(UnmanagedType.Bool)]
    private static extern bool DeleteFile(string name);

    public bool Unblock(string fileName) {
        return DeleteFile(fileName + ":Zone.Identifier");
    }
}

Taken from this answer: Unblock File from within .net 4 c#

edited May 23 '17 at 12:30

Community

1
1

answered Feb 18 '13 at 14:06

markoo

708
1
6
22

i tried the solution but when i used the unblock function it returned false i am using windows 7 x64 maybe this function is not works in x64 or the alternate stream is not present. Do you have any more idea on this function? – fealin Feb 18 '13 at 14:34
Can you verify that the issue was a blocked file? Problems trying to unblock may be permission issues as noted here: http://stackoverflow.com/questions/6375599/looks-this-pinvoke-correct-and-reliable – markoo Feb 18 '13 at 14:57

score 1 · Answer 3 · answered Dec 17 '20 at 22:08

I ran into a similar problem while using the Microsoft.Office.Interop.Excel library. Serkan's answer is totally correct and it pointed me in the right direction but it did not solve my issue. After digging around on some Microsoft blogs I found this solution. There are 3 different constants that the Open() method can take for the last argument.

xlNormalLoad
xlRepairFile
xlExtractData

For me the third one worked. I have to open several different kinds of excel files some of which throw this error and some that don't so I wrapped everything in a try/catch to handle cases where it throws a COMException. You may have been getting a different exception but it is worth trying each of the constants I mentioned above to see if that resolves the issue.

        ExcelApp.Workbook excelBook;
        ExcelApp._Worksheet excelSheet;
        ExcelApp.Range excelRange;
        
        try
        {
            excelBook = excelApp.Workbooks.Open(path);
            excelSheet = excelBook.Sheets[1];
            excelRange = excelSheet.UsedRange;
        }
        catch(COMException)
        {
            excelBook = excelApp.Workbooks.Open(path, CorruptLoad: 
                       ExcelApp.XlCorruptLoad.xlExtractData);
            excelSheet = excelBook.Sheets[1];
            excelRange = excelSheet.UsedRange;
        }

score 0 · Answer 4 · answered Mar 10 '22 at 02:08

You could create a macro with the following code

Sub OpenAndRepairWorkbook()
Dim oWB As Workbook
On Error GoTo Err_Open
  Application.DisplayAlerts = False
  Set oWB = Workbooks.Open(Filename:="C:\ShasurData\ExcelVBA\VBE Tools 2007.xlam", CorruptLoad:=XlCorruptLoad.xlRepairFile)
  Application.DisplayAlerts = True
  Exit Sub

Err_Open:
  MsgBox Err.Number & " - " & Err.Description
  Err.Clear
  Application.DisplayAlerts = True
End Sub

I found this solution right here.

How to repair an excel file?

4 Answers4