Forum Discussion

TCUser_g's avatar
TCUser_g
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
Log.Message(strFilePath&"::"&strSQL)
objConnection.Connectionstring = strConnectionstring
'Open connection for assgined connection string
objConnection.Open
'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
objRecordset.Close
Set objRecordset = Nothing
objConnection.Close
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.

Keyword Tests
Test Run
Web Testing

7 Replies

Sort By
  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    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"
    AConn.Open
    
    Set Cmd = ADO.CreateCommand
    Cmd.ActiveConnection = AConn
    Cmd.CommandText = "SELECT * FROM [Sheet1$]"
    Cmd.CommandType = adCmdText
    
    Set RecSet = Cmd.Execute
    RecSet.MoveFirst
    While Not RecSet.EOF
        Log.Message RecSet("A").Value
        RecSet.MoveNext
    Wend
    
    AConn.Close
End Sub

    and

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

    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
    rraghvani
    Champion Level 3
    2 years ago

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

    • TCUser_g's avatar
      TCUser_g
      New Contributor
      2 years ago

      Thanks for reply,

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

      What to do next, please suggest?

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

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

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    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.