Hello, I have to read an Excel file, update some data and save the file. I wrote small code to open, read, 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")
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()