QTP : Excel As DataBase

 Excel As DataBase

Create an excel book in "c:\1.xlsx" and  name any sheet as "Sheet1" (Default name). In  Sheet 1 , the 1st row is the column names , the rest of the rows forms the data .


CODE BELOW:

Dim objCon, objRecordSet, strExlFile, colCount, row, i

Set objCon = CreateObject("ADODB.Connection")

Set objRecordSet = CreateObject("ADODB.Recordset")

sPath="c:\1.xlsx"

sSheetname="Sheet1"

objCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="& sPath &";Extended Properties=Excel 12.0;Persist Security Info=False"

strSQLStatement = "SELECT * FROM ["& sSheetname &"$]"

objRecordSet.Open strSQLStatement, objCon 'create recordset

colCount = objRecordSet.Fields.count 'Number of columns in the table

While objRecordSet.EOF=false

row=""

For i=0 to colCount-1

row=row &" "& objRecordSet.fields(i)

Next

'Print row 'If you are running this in Qtp you can uncomment this line

msgbox row

objRecordSet.moveNext

Wend

Set objRecordSet = Nothing

objCon.Close

Set objCon = Nothing