Friday, July 03, 2009 3:38 PM
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 :
ReadRow(1, A1, AL)
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.
Monday, July 27, 2009 11:25 PM
Tuesday, August 11, 2009 8:05 PMThe reference to AL is a column, not a cell. A1..AL does not define a range, A1..AL1 would.
Tuesday, October 20, 2009 8:01 PMYou're not releasing the Excel Application. Do .Quit() then release it like the other objects you release.
Thursday, April 19, 2012 8:27 AM
Here is a sample C# code:
ExcelFile ef = new ExcelFile(); // Loads Excel file. ef.LoadXls("filename.xls"); // Selects first worksheet. ExcelWorksheet ws = ef.Worksheets; // Change the value of the cell "A1". ws.Cells["A1"].Value = "Hello world!"; // Saves the file in XLS format. ef.SaveXls("NewFile.xls");
- Edited by Jeff.Atwood Tuesday, July 10, 2012 11:43 AM