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
Use the following code to read the required Excel Sheet as a Database using ADODB Connection in UFT.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
strFileName = "E:\UFT_WorkSpace\TestData\TestExcelFile.xlsx" Dim objCon, objRecSet 'Create an ADODB connection Set objCon= CreateObject("ADODB.Connection") Set objRecSet = CreateObject("ADODB.Recordset") 'Open connection objCon.Open "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & strFileName & ";Readonly=True" 'Check if Error then Exit Function If Err <> 0 Then Reporter.ReportEvent micFail, "Create Connection", "Error occurred while opening Connection. Error : " & Err ExitTest End If 'Execute SQL and store results in reocrdset strSQL = "Select * from [TestData$]" ' Provide the Sheet name of Excel File and append $ symbol at the End of it. objRecSet.Open strSQL, objCon fieldCount = objRecSet.Fields.Count While objRecSet.EOF = False For i = 0 To fieldCount - 1 MsgBox objRecSet.Fields(i) MsgBox objRecSet.Fields("EmpName")'You can also mention the column rather than using Index of Column Next objRecSet.moveNext Wend If Err <> 0 Then Reporter.ReportEvent micFail, "Open Recordset", "Error has occured while reading data.Error Code : " & Err ExitTest End If 'Close and Discard all variables Set objRecSet= Nothing objCon.Close Set objCon = Nothing |
Updating Excel Using ADODB Connection in UFT
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
strFileName = "E:\UFT_WorkSpace\TestData\TestExcelFile.xlsx" Dim objCon, objRecSet 'Create an ADODB connection Set objCon= CreateObject("ADODB.Connection") 'Open connection objCon.Open "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & strFileName & ";Readonly=True" 'Check if Error then Exit Function If Err <> 0 Then Reporter.ReportEvent micFail, "Create Connection", "Error occurred while opening Connection. Error : " & Err ExitTest End If objCon.Execute "Update [TestData$] set Age='28' where EmpName='John'" 'Close and Discard all variables Set objRecSet= Nothing objCon.Close Set objCon = Nothing |
On opening the Excel file the updated value of John’s age will be reflected in the TestData Sheet.
Recommended Posts
- Read, Write and Update Excel File In UFT
- Web Table Methods in UFT With Example
- How to Use Test Combinations Generator in UFT
- Descriptive Programming in UFT with Examples
- DataTable in UFT One | Example of Datatable Methods
- Import Excel File into Datatable in UFT
- How to Download and Install UFT One
- VBScript Loops in UFT
- Read and Update XML File in UFT | VBA
- VBScript MySQL Database Connection in UFT
That is very attention-grabbing, You’re a very professional blogger. I have joined your rss feed and stay up for in search of more of your great post. Also, I’ve shared your website in my social networks!