Problem Saving Excel file from C#/VB.NET

Question

Hello,

   I have to read an Excel file, update some data and save the file. I wrote small code to open, save and close an excel file. I am using Microsoft.Office.Interop to achieve the goal. I have set the Excel application visibility as false. I tried the same in C# and VB.NET, but am facing same problem. Code is below:-

    Private Sub OpenFile()
        Try
            excelApp = New Excel.ApplicationClass()
            excelApp.Visible = False
            excelworkbook = excelApp.Workbooks.Open(workbookPath, 0, False, 5, "", "", False, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", True, False, 0, False, False, False)
            excelSheets = excelworkbook.Worksheets
            excelWorkSheet = excelworkbook.Worksheets("Sheet1")  '(Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item("Sheet1")
            MessageBox.Show("Connected and Opened")
            range = excelWorkSheet.UsedRange
            MsgBox("Rows = " + range.Rows.Count.ToString + " Cols = " + range.Columns.Count.ToString)
        Catch e As Exception
            excelSheets = Nothing
            excelworkbook = Nothing
            excelWorkSheet = Nothing
            excelApp = Nothing
            MessageBox.Show("Exception MSG " + e.Message + "\n ST :" + e.StackTrace)
        End Try
    End Sub

    Private Function ReadRow(ByVal rowNo As Integer, ByVal startRange As String, ByVal endRange As String) As Array
        Dim rge As Excel.Range = Nothing
        Dim valuesList As Array = Nothing

        Try
            rge = excelWorkSheet.Range(startRange, endRange)
            valuesList = rge.Cells.Value
        Catch e As Exception
            MsgBox("Uable To Read from " + startRange + " To " + endRange + " Due to " + vbCrLf + e.StackTrace)
            Save_Close()
        End Try

        Return valuesList
    End Function

    Private Sub Save_Close()
        Try
            excelworkbook.Save()
            excelworkbook.Close()
        Catch ee As Exception
            MessageBox.Show("Unable To Save : " + ee.Message)
            excelworkbook.Close()
        End Try

        releaseObject(excelApp)
        releaseObject(excelworkbook)
        releaseObject(excelWorkSheet)
        releaseObject(excelSheets)

        MessageBox.Show("File Closed")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

Methods I have called is :
OpenFile()
ReadRow(1, A1, AL)
Save_Close


But the problem I am facing is:
For REad Row: I get Exception
Unable to Read from A1 to AL Due to
Exception from HRESULT : 0X800A03EC
st: AT ....WorkSheet.getRange(Object Cell1, Object Cell2) at ....Line no

I tried the same code with ReadRow parameters as 1, A1, I6) and I got an array successfully. I want to read contents of a row of the provided range.

On calling excelworkbook.Save() in Save_Close(), I get exception as follows and the system halts/hangs on close line. I have to Stop Debugging.
Unable to save: Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))


After once executing, the next time I run the application, I get on start, "File is being used by , want to open as read-only?" with Yes/No options.

Can anybody help me know where am I going wrong. The file is .xls file with fixed 1st 3 cols and has several cols till CL or even more. My OS is Vista. I have added reference of Microsoft.Office.Interop.Excel version 12. I tried with 11 also, but faced same error. I want to edit the file also, so it should be opened with ability to update and not just Read-Only.


Any help is appreciated. Please try to help at the earliest.


Thanks

Answer 1

Tried this: Excel LCID or Locale issue - HRESULT: 0x800A03EC / Old format or invalid type library

Answer 2

The reference to AL is a column, not a cell. A1..AL does not define a range, A1..AL1 would.

Answer 3

You're not releasing the Excel Application.  Do .Quit() then release it like the other objects you release.

Answer 4

Hi,

you can also try this C# / VB.NET Excel component to easily open, modify and C# save Excel file.

Here is a sample C# code:

ExcelFile ef = new ExcelFile();
 
// Loads Excel file.
ef.LoadXls("filename.xls");
 
// Selects first worksheet.
ExcelWorksheet ws = ef.Worksheets[0];
 
// Change the value of the cell "A1".
ws.Cells["A1"].Value = "Hello world!";
 
// Saves the file in XLS format.
ef.SaveXls("NewFile.xls");