Posts

Showing posts with the label Excel

How to access Excel as a database using UFT

Whenever we access excel through UFT, we always use the createobject method which blocks the excel and starts excel.exe process. It can result in problems if the test script stops abruptly. To resolve this issue you can access the excel as a database through UFT. Below code will help you with that.         Dim oConn, oRS, arrData, x,iStart         CONST adOpenStatic = 3         CONST adLockOptimistic = 3         CONST adCmdText = "&H0001"         Set oConn = CreateObject("ADODB.Connection")         Set objRecSet= CreateObject("ADODB.RecordSet")          'Open Connection         oConn.Open "Provid...

how to access the excel file as database in UFT?

We mostly use the excel file in UFT by using the excel object but it can also be accessed as a database and you can run the queries on different excel sheets as the database tables. You can use the ADODB connection to use the excel as database. Below is the code snippet to use excel as a database. Set  Conn =  CreateObject ( "ADODB.Connection" ) Set  RecSet=  CreateObject ( "ADODB.RecordSet" ) Conn.Open  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="  & ExcelFilePath&  ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"";" If you want to access the .xlsx file then please change "Excel 8.0" to "Excel 12.0". After making the successful connection, you need to provide the query and execute it using the recordset in the same way you do it for database tables. But here you need to provide the excel sheet name as database tables.  Query =  "Select * From ["  & sheetName &  ...