Pages

Monday, March 7

Using excel sheet as database table

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.


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

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




Screen-shots of sample excel and output are following -




In case of any queries, please post your comments.

17 comments:

  1. Hi Abhikansh,
    I 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!!

    ReplyDelete
  2. @Smiles
    thnx for your inputs. code modified!

    ReplyDelete
  3. Really appreciate you for putting all the code up in one website. Thanks a lot..

    ReplyDelete
  4. Hi Abhikansh your efforts are Highly praiseworthy !!

    ReplyDelete
  5. Hi
    Can u give code for sorting excel data after exporting it to Record set
    Thiru

    ReplyDelete
  6. I just need one clarification on one thing....
    Can 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.

    ReplyDelete
  7. While executing code,,i am getting error message stating "[Microsoft][odbc Device Manager]Data source name not found and no default drivers specified........
    Note: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

    ReplyDelete
    Replies
    1. Even I am getting the Same error and I am using UFT 12.1 Trail Version.. Please help.

      Delete
    2. hi,
      if 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"


      Delete
    3. despite giving this i get the same Error. Could you please help. Im using a notepad and saving it as .vbs file

      Delete
  8. Superb Abhi , its working awesome . Thank you

    ReplyDelete
  9. Hi Abhishe,

    How to update excel sheet by using ADODB connection?

    Could you please help?

    ReplyDelete
  10. Thank you so much .its working great

    ReplyDelete
  11. Thank 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.

    ReplyDelete
  12. is this code work for excel 2013 and OS window 10

    ReplyDelete
  13. Rajendra Narayan Mahapatra2 November 2017 at 10:35

    The below code worked for me

    Set 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

    ReplyDelete