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.
Posted by at

19 comments:

  1. Smiles19 March 2011 at 13:35

    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. Abhikansh Jain19 March 2011 at 16:30

    @Smiles
    thnx for your inputs. code modified!

    ReplyDelete
  3. Anonymous28 April 2012 at 06:23

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

    ReplyDelete
  4. Sangita23 September 2013 at 08:23

    Hi Abhikansh your efforts are Highly praiseworthy !!

    ReplyDelete
  5. Unknown21 February 2014 at 10:33

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

    ReplyDelete
  6. atulparate14 March 2014 at 07:27

    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. Anonymous25 September 2014 at 17:17

    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. Unknown20 December 2014 at 03:03

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

      Delete
    2. Anonymous26 June 2015 at 06:25

      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. Anita16 May 2017 at 10:57

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

      Delete
  8. Anonymous4 March 2015 at 08:01

    Superb Abhi , its working awesome . Thank you

    ReplyDelete
  9. Murali J3 March 2017 at 01:46

    Hi Abhishe,

    How to update excel sheet by using ADODB connection?

    Could you please help?

    ReplyDelete
  10. Venkataiah Nitta27 April 2017 at 15:21

    Thank you so much .its working great

    ReplyDelete
  11. Unknown15 June 2017 at 08:24

    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. bibhu16 June 2017 at 05:22

    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
  14. Deepika12 October 2018 at 07:58

    Excellent informative blog, keep for sharing.

    Best System Integration services | Massil Technologies

    ReplyDelete
  15. kickass torrents31 January 2020 at 10:52

    Good article and knowledge for me! I found a lot of information here!

    ReplyDelete
  16. Pooja15 June 2021 at 14:42

    Hi I am getting the below error .UFT version 14.0,excel format:xls

    Error msg:
    [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

    ReplyDelete

Subscribe to: Post Comments (Atom)