Recently I face an issue on Excel 2016. After i close the connection string on excel 2016 environment the system still lock the excel from editing (say that file in use). but the code is working perfectly on excel 2007 and 2010 environment (after connection close, the file is ready for my next function to open and edit).
on excel 2016 environment it seem like the file need to locked for 75 second after that it automatically unlocked, but this won't happen on excel 2007 and 2010 environment
where should i change it?
Public Function gf_ExcelConnection(ByRef strLocation As String, ByRef sWorksheetName As String, _
ByRef lExcelDataset As DataSet, ByRef iLastRow As Integer, _
ByRef sColumn As String) As Boolean
Dim strExcelConn As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strLocation & _
"; Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;"""
Dim ExcelConn As New System.Data.OleDb.OleDbConnection(strExcelConn)
Dim ExcelCommand As OleDbCommand = New OleDbCommand
Dim ExcelDataAdapter As New OleDbDataAdapter
Try
gf_ExcelConnection = True
ExcelCommand.CommandText = "SELECT * FROM [" + sWorksheetName + "A1:" & sColumn & iLastRow & "] "
ExcelDataAdapter.SelectCommand = ExcelCommand
ExcelDataAdapter.SelectCommand.Connection = ExcelConn
MsgBox(103)
ExcelConn.Open()
ExcelDataAdapter.Fill(lExcelDataset, "ImportTable")
ExcelConn.Close()
ExcelConn.Dispose()
MsgBox(104)
Catch ex As Exception
gf_ExcelConnection = False
gclsError.gf_ErrorHandler("InvalidIncorrectFile", Windows.Forms.MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Error, False)
ExcelConn.Close()
Finally
ExcelConn.Close()
ExcelConn.Dispose()
ExcelConn = Nothing
MsgBox(105)
End Try
End Function
