I have developed a VB.Net code for retrieving data from excel file .I load this data in one form and update it back in excel after making necessary modifications in data. This complete flow works fine but most of the times I have observed that even if I close the form; the already loaded excel process does not get closed properly.
I tried all possible ways to close it but could not be able to resolve the issue. Find below code which I am using for connecting to excel and let me know if any other approach I may need to follow to resolve this issue.
Note: I do not want to kill the excel process as it will kill other instances of the excel
Dim connectionString As String
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ExcelFilePath & ";
Extended Properties=excel 8.0; Persist Security Info=False"
excelSheetConnection = New ADODB.Connection
If excelSheetConnection.State = 1 Then excelSheetConnection.Close()
excelSheetConnection.Open(connectionString)
objRsExcelSheet = New ADODB.Recordset
If objRsExcelSheet.State = 1 Then objRsExcelSheet.Close()
Try
If TestID = "" Then
objRsExcelSheet.Open("Select * from [" & ActiveSheet & "$]", excelSheetConnection, 1, 1)
Else
objRsExcelSheet.Open("Select Test_ID,Test_Description,Expected_Result,Type,UI_Element,Action,Data,Risk from [" & ActiveSheet & "$] WHERE TEST_Id LIKE '" & TestID & ".%'", excelSheetConnection, 1, 1)
End If
getExcelData = objRsExcelSheet
Catch errObj As System.Runtime.InteropServices.COMException
MsgBox(errObj.Message, , errObj.Source)
Return Nothing
End Try
excelSheetConnection = Nothing
objRsExcelSheet = Nothing
