Forum Discussion

TCUser_g's avatar
New Contributor
2 years ago

Test Complete Unable to load URL from Excel

Working on Test Complete Keyword driven framework, which is now migrated from Win7 to Win10. All the environment URLs configuration are in Excel sheet.
Issue: During execution once the browser launched URL is not opening
Debug: While debugging found one error .
Error: Operation is not allowed when the object is closed.

Below is the code to read from Excel:


Function GetXLSRecordset(strFilePath, strSQL)
On Error Resume next
Set objConnection = CreateObject("ADODB.Connection")
'Create Connection srting
strConnectionstring = "Provider=Microsoft.ACE.OLEDB.16.0;" & _
"Data Source=" & strFilePath & ";" & _
"Extended Properties=Excel 16.0"

Log.Enabled = True
objConnection.Connectionstring = strConnectionstring
'Open connection for assgined connection string
'Create new recordset
Set objRecordset = CreateObject("ADODB.Recordset")
'Execute SQL Query
objRecordset.Open strSQL, objConnection, adOpenStatic, adLockReadOnly
record_Count = objRecordset.RecordCount
Log.Message("Record Count: "&record_Count)

Log.Enabled = True
Set GetXLSRecordset = objRecordset
Set objRecordset = Nothing
Set objConnection = Nothing
End Function




System Configuration:
TestComplete 14.x 32bit
MS Excel 16.x 32bit
MS Access DB driver 16.x 32 bit
OS Win 10


Every time I'll try to print `objRecordset` getting undefined, but it returns the count `objRecordset.RecordCount`

Tried all the possible solution found on community support but didn't work, please suggest me some workaround.

Everything is working properly on win7 machine.

7 Replies

  • rraghvani's avatar
    Champion Level 3

    Here are two examples that work on Windows 10 with the appropriate ODBC drivers.

    Sub TestProc()
        Set AConn = ADO.CreateConnection
        AConn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=C:\Temp\Book1.xlsx"
        Set Cmd = ADO.CreateCommand
        Cmd.ActiveConnection = AConn
        Cmd.CommandText = "SELECT * FROM [Sheet1$]"
        Cmd.CommandType = adCmdText
        Set RecSet = Cmd.Execute
        While Not RecSet.EOF
            Log.Message RecSet("A").Value
    End Sub


    Sub TestProc1()
        Set Conn = CreateObject("ADODB.Connection")
        Conn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=C:\Temp\Book1.xlsx"
        Set Rs = CreateObject("ADODB.Recordset")
        Rs.Open "[Sheet1$]", Conn, adOpenStatic, adLockReadOnly
        While Not Rs.EOF
            Log.Message Rs.Fields.Item("A").Value
    End Sub
  • TCUser_g's avatar
    New Contributor

    Hi, Thanks for the answer.

    I followed this but no success and after digging deep into it, found one error

    ("Error: Provider cannot be found. It may not be properly installed.")

    because of which getting above mentioned error ("Error: Operation is not allowed when the object is closed.")

    I have checked the MS Access 32 bit driver is installed.

    used this command for installation: c: accessdatabaseengine2016_32.exe /quiet

    Also Verified that all related softwares(Test complete, MS Office 365 and Access Driver) are 32 bit.

  • rraghvani's avatar
    Champion Level 3

    If you type in ODBC, you should be able to see the drivers you have installed

    • TCUser_g's avatar
      New Contributor

      Thanks for reply,

      Can see the 32 bit driver name in the list:(Edited)

      What to do next, please suggest?

  • rraghvani's avatar
    Champion Level 3

    Which driver are you using, Microsoft.ACE.OLEDB.16.0 ?

  • rraghvani's avatar
    Champion Level 3

    If you're trying to access an Excel document, then should you not be using the connection string Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) as opposed to using MS Access driver?

    On your desktop, create a Text Document file, and change the file extension from .txt to .udl (Microsoft Data Link file). Double click the file, and Data Link Properties dialog will appear. Select your Provider and enter the appropriate details for Connection. If you Test Connection, you will see that it doesn't work. However, if you provide a MS Access file as your Data Source, and enter the login information for the DB. Test Connection will then work.

    If you opend the .udl in Notepad, you'll see the actual connection string.