Read Excel Using ADODB Connection in UFT | VBA

If you’re reading this blog post, chances are that you have written an automation test script and are planning to parameterized the hard-coded values with dynamic input from excel or you want to update Excel cell values using ADDOB connection in UFT and now you are wondering how to do it.

The Excel sheet is more often used in UFT to read input data dynamically at runtime. In this article, I will show you how you can read excel using the ADODB connection at runtime using UFT.

Starting from the Basic Concepts

When we read an Excel sheet using ADODB connection the first row of the sheet is considered as Database Column and the rest of the rows are considered as Database records.

Read Excel Using ADODB Connection in UFT

Read Excel Using ADODB Connection in UFT

Use the following code to read the required Excel Sheet as a Database using ADODB Connection in UFT.

Updating Excel Using ADODB Connection in UFT

Let’s say we have an Excel file named “TestExcelFile.xlsx”. In this Excel file, there is a sheet  “TestData” that contains the employee details of an organization as shown below.
Let’s update the age of Employee John from 25 to 28. The SQL query to update John’s record would be “Update [TestData$] set Age=’28’ where EmpName=’John’“. We can use the following code to execute an update query. Close the Excel file before executing the code else it will throw a runtime error.

On opening the Excel file the updated value of John’s age will be reflected in the TestData Sheet.


Recommended Posts

Leave a Reply