How to Read Data from Excel by Using ADODB Object?

ADODB is one of the ways to read the data from Excel . There are totally 6 steps we have to follow to read data from Excel by using ADODB object. They are listed below:

  1. Creating a ADODB object
  2. Creating a connection String
  3. Writing an SQL query
  4. Creating a record set object
  5. Executing the SQL query and storing the result in record set object
  6. Then finally reading the data from that record set object

For example look at the below example to know more about how to read from Excel using ADODB object

strfilepath=”E:\DATA_Driven_Current\TestData\EMP.xlsx”

//Path of the file from where you want to read the data
Set ODB=CreateObject(“adodb.Connection”)

//Creating  the ADODB object

ODB.ConnectionString= “Provider=Microsoft.Ace.Oledb.12.0; Data Source=”&strfilepath&”; Extended Properties=Excel 8.0″
ODB.Open

sql= “Select * from Create_Emp”

//writing SQL query to fetch the data from Excel

Set oRec=CreateObject(“adodb.Recordset”)

//Creating the Recordset object

oRec.Open sql,ODB

//open the recordset object  and executing the SQL  query
    strFirstName=oRec.Fields(“First_Name”).Value

//Reading the values from the filed
    msgbox strFirstName
    
oRec.Close
ODB.Close

//finally we have to close opened resources ( data base object and record set object)

15 thoughts on “How to Read Data from Excel by Using ADODB Object?”

  1. Hi! I simply would like to give you a big thumbs up for your excellent information you have right here on this post. I will be coming back to your web site for more soon. Tyne Bran Karyn

  2. Hello I am so thrilled I found your site, I really found you by accident, while I was searching on Aol for something else, Anyhow I am here now and would just like
    to say thanks a lot for a fantastic post and
    a all round entertaining blog (I also love the theme/design), I don’t have time to read it all at the moment but I have saved it and also included your RSS feeds,
    so when I have time I will be back to read more, Please do
    keep up the great job.

  3. Thanks on your marvelous posting! I really enjoyed reading it, you happen to be a
    great author. I will be sure to bookmark your blog
    and will eventually come back down the road.

    I want to encourage you continue your great job, have a nice day!

  4. Hey, I think your blog might be having browser compatibility issues.
    When I look at your website in Ie, it looks fine but when opening
    in Internet Explorer, it has some overlapping.
    I just wanted to give you a quick heads up! Other then that, superb blog!

  5. I do agree with all the ideas you have presented in your
    post. They are very convincing and will
    definitely work. Still, the posts are too
    quick for newbies. May just you please lengthen them a bit from next time?
    Thanks for the post.

Leave a Reply

Your email address will not be published. Required fields are marked *