How to Read Data from Excel by Using ADODB Object?

July 18, 2020

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)