Excel sheet can be used as a database for the parameterization purpose. Following code demonstrate how to connect and consider excel sheet as database table.
This might be usefull while working with databases. You can export database table into excel (one time) and then work on excel as database.
Set objCon = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
strExlFile = "C:\abhikansh.xls"
objCon.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &strExlFile & ";Readonly=True"
strSQLStatement = "SELECT * FROM [Sheet2$]"
objRecordSet.Open strSQLStatement, objCon 'create recordset
colCount = objRecordSet.Fields.count 'No of columns in the table
While objRecordSet.EOF=false
row=""
For i=0 to colCount-1
row=row &" "& objRecordSet.fields(i)
Next
Print row
objRecordSet.moveNext
Wend
Set objRecordSet = Nothing
objCon.Close
Set objCon = Nothing
In case of any queries, please post your comments.
Hi Abhikansh,
ReplyDeleteI was wondering that the above code should work fine even without adding the for loop after while loop as recordset object would point to first row by default and would keep looping till the condition becomes true.
also, just a suggestion, it would be great if you can print the output to show the format of it.
Thanks!!
@Smiles
ReplyDeletethnx for your inputs. code modified!
Really appreciate you for putting all the code up in one website. Thanks a lot..
ReplyDeleteHi Abhikansh your efforts are Highly praiseworthy !!
ReplyDeleteHi
ReplyDeleteCan u give code for sorting excel data after exporting it to Record set
Thiru
I just need one clarification on one thing....
ReplyDeleteCan we use multiple tables in one sheet as i think we can only use one table in one sheet?
Is there any possibility of using multiple tables under one sheet.
While executing code,,i am getting error message stating "[Microsoft][odbc Device Manager]Data source name not found and no default drivers specified........
ReplyDeleteNote:I am executing code via notepad(.vbs) instead from qtp....
Below is the code
Dim objCon, objRecordSet, strExlFile, colCount, row, i
Set objCon = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
strExlFile = "C:\Desktop\qtp_Excel.xlsx"
objCon.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &strExlFile & ";Readonly=True"
strSQLStatement = "SELECT * FROM [Sheet1$]"
objRecordSet.Open strSQLStatement, objCon 'create recordset
colCount = objRecordSet.Fields.count 'No of columns in the table
While objRecordSet.EOF=false
row=""
For i=0 to colCount-1
row=row &" "& objRecordSet.fields(i)
Next
Print row
objRecordSet.moveNext
Wend
Set objRecordSet = Nothing
objCon.Close
Set objCon = Nothing
Even I am getting the Same error and I am using UFT 12.1 Trail Version.. Please help.
Deletehi,
Deleteif you are using excel sheet of .xlsx extn then you need to use below code for the connection
objCon.Open "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" &strExlFile & ";Readonly=True"
despite giving this i get the same Error. Could you please help. Im using a notepad and saving it as .vbs file
DeleteSuperb Abhi , its working awesome . Thank you
ReplyDeleteHi Abhishe,
ReplyDeleteHow to update excel sheet by using ADODB connection?
Could you please help?
Thank you so much .its working great
ReplyDeleteThank you so much. After searching all over I got here what I was looking for. I appreciate you. and It is really working great. Thanks for your effort.
ReplyDeleteis this code work for excel 2013 and OS window 10
ReplyDeleteThe below code worked for me
ReplyDeleteSet ObjCon = CreateObject("ADODB.Connection")
Set ObjRs = CreateObject("ADODB.Recordset")
strExcelPath = "C:\UFT\TestData\TestData.xls"
strSheetName = "TestData"
strSqlStmt = "SELECT * from [" & strSheetName & "$]"
ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &strExcelPath& ";Readonly=True"
ObjCon.Open ConnectionString
ObjRs.Open strSqlStmt,ObjCon
While ObjRs.EOF = False
data =""
For Iterator = 0 To ObjRs.Fields.Count-1 Step 1
data = data&" "&ObjRs.Fields(Iterator)
Next
print data
ObjRs.MoveNext
Wend
ObjRs.Close
ObjCon.Close
Set ObjRs = Nothing
Set ObjCon = Nothing
Excellent informative blog, keep for sharing.
ReplyDeleteBest System Integration services | Massil Technologies
Good article and knowledge for me! I found a lot of information here!
ReplyDeleteHi I am getting the below error .UFT version 14.0,excel format:xls
ReplyDeleteError msg:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.