Quick test professional

QTP Tips QTP codes QTP Faqs and more

ADODB CONNECTION TO READ DATA FROM EXCEL SHEET

Function GetContentFromDB (strFileName, strSQLStatement)

 

Dim objAdCon, objAdRs

 

Set objAdCon = CreateObject(“ADODB.Connection”)

 

objAdCon.Open “DRIVER={Microsoft Excel Driver (*.xls)};DBQ=”&strFileName & “;Readonly=True”

If Err <> 0 Then

      Reporter.ReportEvent micFail,”Create Connection”, “[Connection] Error has occured. Error : ” & Err

      Set obj_UDF_getRecordset = Nothing

   Exit Function

End If

Set objAdRs = CreateObject(“ADODB.Recordset”)

objAdRs.CursorLocation=3                        ‘ set the cursor to use adUseClient – disconnected recordset

objAdRs.Open strSQLStatement, objAdCon, 1, 3

 

MsgBox objAdRs.fields(4).name

While objAdRs.EOF=false

   For i=0 to objAdRs.Fields.count

Msgbox objAdRs.fields(i)

Next

objAdRs.moveNext

Wend

 

If Err<>0 Then

      Reporter.ReportEvent micFail,”Open Recordset”, “Error has occured.Error Code : ” & Err

      Set obj_UDF_getRecordset = Nothing

     Exit Function

End If

 

Set objAdRs.ActiveConnection = Nothing

 

objAdCon.Close

Set objAdCon = Nothing

 

 

 

End Function

 

Set rsAddin = GetContentsFromDB(“C:\Documents and Settings\mohank\Desktop\Login.xls”, “Select * from [Login$]”)

Author:Mohan Kakarla

Advertisements

February 14, 2008 - Posted by | Excel Automation | , , , , , , , ,

34 Comments »

  1. Hello Mohan,

    Thanks a lot for the code snipet provided above.

    It really worked for me.

    Can you please add me into your community so that myself also share my experiences while using QTP.

    Thanks and Best Regards,
    Shripad Oka

    Comment by Shripad Oka | March 19, 2008 | Reply

    • hi mohan ,thanks for the code ,its very helpfull could you please tel me what is [Login$] how is is it in the table

      Comment by raghav | April 19, 2013 | Reply

  2. Hello Mohan,

    Thanks a lot for the code snipet provided above.

    It really verymuch helpful for me.

    Can you please add me into your community so that myself also share my experiences while using QTP.

    Thanks and Best Regards,
    Manohar makineni

    Comment by manohar | April 4, 2008 | Reply

  3. HI,

    How to identify tool bar/objects in .pdf file using with QTP Tool 8.2/9.2

    Comment by srinivas shaga | May 2, 2008 | Reply

  4. Hi,
    How to make connection with excel file using QTP

    Comment by Vineet Kandpal | June 18, 2008 | Reply

  5. Hi Mohan,
    Thanks for the code. However I am new to QTP and vbscript.

    Please can you explain to me the purpose of [Login$].

    WHere can i found more documentation on this syntax.

    Thank you.
    regards
    minkey

    Comment by minkey | June 25, 2008 | Reply

    • hi minkey,

      “Select * from [Login$]” is SQLStatement
      In this statement,Login is SheetName, so suppose you want to access sheet “TestEnv” then u have to write following SQLStatement:
      Select * from [TestEnv$]

      Comment by Jay | July 17, 2009 | Reply

  6. Is there any way that we can export a runtime variable value to an external excel file?

    Comment by Val | June 25, 2008 | Reply

  7. minkey,

    [Login$] is the sheet name in the excel spread sheet.

    Comment by quicktestprofessional | June 26, 2008 | Reply

  8. Hello Mohan

    This is wat I was looking for past few weeks. Thanks for your input.

    I tried executing this code but it fails on the following line of code
    objAdRs.Open strSQLStatement, objAdCon, 1, 3
    Error: [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object ‘TestEnv’. Make sure the object exists and that you spell its name and the path name correctly.
    Line (99): “objAdRs.Open strSQLStatement, objAdCon’, 1, 3”.

    I have my excel file name as ‘testenv1.xls’ and named the sheet to ‘TestEnv’ where the data exists.

    Comment by Balaji | July 18, 2008 | Reply

  9. I didn’t understand these two lines
    1.objAdRs.CursorLocation=3
    2.objAdRs.Open strSQLStatement, objAdCon, 1, 3

    what does number 3 represent in first line?

    waht does the use of last two arguments i.e 1,3 in second line?. without these arguments also my code is executing succesfully.

    can u please clarify this, so that i can get complete idea on this script

    Comment by Lokanadham | October 20, 2008 | Reply

  10. Lokanadham ,
    Read the comment 15 & 16 to get the idea

    Comment by quicktestprofessional | October 20, 2008 | Reply

  11. hi

    how to give future date in the script it should validate
    it has to validate if day=30,31 and 29

    if month is 1 or 12

    please give me an idea

    day=day(now)+1

    it adds 1 day to current date if day is 31 of the month while execution of script then it will get fail.please help me

    Comment by Vimala | December 9, 2008 | Reply

  12. Hi Mohan

    Thanks for sharing this code snippet. But I am new to QTP and need somem ore guidance from u. I am using QTP 9.5 and I need to run a select query on my excel sheet.

    Before I start using your code snippet (copy/modify it in a qfl in QTP) do I need to chaneg any settings in QTP?

    Please guide as to what steps need to be done before this code can be put to use. As of now “EXPECTED EXPRESSION” errors are coming on most of the lines.

    Please guide ASAP.

    Comment by Charu | March 18, 2009 | Reply

  13. HI

    When I am using the above function I am gettign the type mismatch error at the function call. what shoul I put in the sql place.
    Please suggest

    Thanks
    Uma

    Comment by Uma | April 10, 2009 | Reply

  14. I didn’t understand these two lines
    1.objAdRs.CursorLocation=3
    2.objAdRs.Open strSQLStatement, objAdCon, 1, 3

    what does number 3 represent in first line?

    waht does the use of last two arguments i.e 1,3 in second line?. without these arguments also my code is executing succesfully.

    could u please clarify this, so that i can get complete idea on this script

    Comment by Maheshwar | April 22, 2009 | Reply

  15. The code – objAdCon.Open “DRIVER={Microsoft Excel Driver (*.xls)};DBQ=”&strFileName & “;Readonly=True” works only when I try to make a single connection. But when I try to make two connections to the ‘same excel file’ from two different programs, it gives a runtime error – “[Microsoft][ODBC Excel Driver] Cannot open database ‘(unknown)’. It may not be a database that your application recognizes, or the file may be corrupt”. Can someone help me in this?

    Comment by chitta | May 18, 2009 | Reply

  16. Hi,
    Cud u provide me the script for the below:
    1)get the data from DB2
    2)generate the excel sheet
    3)save it
    Thanks

    Comment by bobby | June 12, 2009 | Reply

  17. hi mohan,,

    good work from you… can u provide the complete code to read the particular cell value by refering the primary key in qtp.. like consider the following table

    Pk

    TC001 kalyan kumar nisha preethi
    TC002 kumar nishith nimala
    TC003 janu priya suji hema rathi

    i have tried with ur code but am not able to fetch the exact cell value by refering the Pk=”TC001″.through recorset

    its worked by another method like datatable.import.. but i wanna get it done through by ADODB.Connection and Recorset..
    please help me out yaaar ASAP.

    Regards,
    Dhesi

    Comment by Dhesihan | January 27, 2010 | Reply

  18. Plz notify me about the new updates

    Comment by Kamalpreet Kaur | February 4, 2010 | Reply

  19. hi,

    your presentation is very clear and good.
    can i have detail information about frameworks, particularly
    hybrid framework, pros and cons, how we will work on it and the implementation,and please notify me about ur new updates.

    thank you,
    hari.

    Comment by hari | February 10, 2010 | Reply

  20. Hi Mohan,

    Useful code..

    Thank you..
    santosh

    Comment by santosh | March 19, 2010 | Reply

  21. Hello Mohan,

    I am actually struck up at a scenario. The scenario is as follows.

    There are 958 rows and 19 columns in a web table. And I need to sort out the data which satisfies certain criteria. I am able to sort the data using my code. The data is same as the one in the application but the rows are in different order. Like the 1st row my sorted excel sheet is in 4th row in application web table. now I need to compare the values of my sorted excel sheet and the web table rows. Is there a way to compare.

    Sorry, I think I confusing you and not clear to you. To be short, i just want a way to compare two excel sheets and check whether they have the same data or not. The problem is the rows in the excel sheets are same but in different order.

    Comment by Sirisha | March 25, 2010 | Reply

  22. Hello Mohan,

    Thanks for the code.
    However it can’t fetch more than 255 characters from a cell.
    I have formatted the cells as ‘Text’, but still it can’t fetch more than 255 characters.

    I would really appritiate if you can provide some help on this.

    Thanks once again…

    Comment by Vishvanath | May 29, 2010 | Reply

  23. hello mohan

    your code is alright
    the adodb works perfect except in one case:

    If m_objRs.EOF And m_objRS.BOF

    the first value of the excelsheet isn’t filled. the cursor just gets into the correct field & nothing … is there any workaround for this constellation ? im not sure if the recordset is disconnected. please help !

    here’s my defintion:

    Option Explicit
    Class PropertyMgr
    Private m_arrProperties ‘Tab-seperated Strings containing all cached properties, watch memory usage!
    Private m_objConn ‘ADO connection object to Access-Property-Database
    Private m_objRs ‘RecordSet holding the result of the query

    Private Sub Class_Initialize
    Set m_objConn = CreateObject(“ADODB.Connection”)
    Set m_objRs = CreateObject(“ADODB.RecordSet”)
    m_objConn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=””” & c_strClassDir & “properties.mdb”””
    m_arrProperties = Array()
    End Sub

    Private Sub Class_Terminate
    If m_objRs.State = 1 Then
    m_objRs.Close
    End If

    Set m_objRs = Nothing

    If m_objConn.State = 1 Then
    m_objConn.Close
    End If

    Comment by sandeman | August 5, 2010 | Reply

  24. there is some big mistake in code , the name of function defined and called are different

    Comment by vipin garg | February 12, 2011 | Reply

  25. Hi Mohan,

    I is working fine with excel 2003 but what modification we have to in order to work with excel 2007

    Comment by Mrinal | April 13, 2011 | Reply

  26. Hi Mohan,

    It is working fine with excel 2003 but what modification we have to in order to work with excel 2007.

    Thanks

    Comment by Mrinal | April 13, 2011 | Reply

  27. Hi Mohan,
    Can you give me an idea to insert a row in excel with ‘n’ columns?
    Is this possible or only we can enter entire row?

    Comment by Arun | October 27, 2011 | Reply

  28. Hi your Coding is helpful for me

    i Have one more error How to Use Join functions in ADODB Excel Sheet Connection .. I Have Tried but the join is not supported and also Late Binding is not Supported..

    I need your Response

    Comment by Kalyan | December 5, 2011 | Reply

  29. how do you get different information from different worksheets within a workbook? how do get total number of worksheets in a workbook? and their names?

    Comment by Rahul | January 18, 2012 | Reply

  30. HI Mohan,

    It really helped alot…
    I am getting small error.
    I have 500 records in excel sheet, somtimes recordset fetches NULL values even though cell has proper value.

    If my excel has value 118 in first row and third column, it fetches it as NULL.
    This is not consistent, it may happen for random cells.
    Please help me out ASAP.

    Comment by Anand Shirkande | February 29, 2012 | Reply

  31. i need to export the data from database to excel using adobd (use select column name into table[excelsheet] from table[database table])

    Comment by satish devulapalli | January 8, 2013 | Reply

  32. Hi Mohan,
    Here i have a doubt while using ADODB connection to export data to excel we are getting FACILITY_ITF error. Could you plz help us to resolve this issue. Thanks in advance.

    Comment by Rajendra | October 26, 2013 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: