Hi,
I am using Microsoft excel driver available for xlsx as given in below line
objAdCon.Open "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ="&strFileName & ";Readonly=True"
I have corrected the code which was giving connection error. Once code works fine but next time TestComplete hangs.
Excel process remains in memory and TestComplete keeps the file locked. I have closed excel process at the start of script as well as at the end of the script. Also I have closed excel process before reading the input.
After TestComplete restart the memory gets released and then TestComplete again works fine.
The same problem I found on Smart Bear community. There also they have mentioned that close the TestComplete to release excel process if such issue occurs.
Also they have suggested to close excel process that I am already doing.
Please refer below threads on Smart Bear Community.
http://community.smartbear.com/t5/Functional-Web-Testing/Releasing-Excel-Processes/td-p/78190
http://community.smartbear.com/t5/Functional-Web-Testing/Unable-to-access-the-excel-file/td-p/74792
Here is my code. Please let me know if anything is wrong
Function ReadInputFromExcel(sheetName, settingName, columnName)
On Error Resume Next
Call closeExcel
log.Message "In ReadInputFromExcel"
log.Message sheetName
Dim objAdCon, objAdRs
strFileName = Project.Variables.InputFilePath
log.Message strFileName
strSQLStatement = "Select "& columnName &" from ["& sheetName &"$] where SettingName = '"& Trim(settingName) &"'"
log.Message strSQLStatement
Set objAdCon = CreateObject("ADODB.Connection")
objAdCon.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ="&strFileName & ";Readonly=True"
'objAdCon.Open "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ="&strFileName & ";Readonly=True"
Set objAdRs = CreateObject("ADODB.Recordset")
objAdRs.Open strSQLStatement, objAdCon
AqUtils.Delay 1000
If Err <> 0 Then
log.message Err.Number
log.Message Err.Description
objAdRs.close
objAdCon.Close
objAdRs = Nothing
objAdCon = Nothing
log.Error "Create Connection Error",Err
Exit function
End If
val = objAdRs(""& columnName &"").Value
log.Message val
'return value
objAdRs.close
objAdCon.Close
objAdRs = Nothing
objAdCon = Nothing
log.Message objAdRs.State
log.Message objAdCon.State
Call closeExcel
ReadInputFromExcel = val
End Function