How to read excel using ADODB connection

Excel workbooks are the most common and an excellent way of storing data. Did you know you can connect to the excel workbook as a database using vbscript and read data from it? Amazing isn’t it?  Let’s see how to read excel using adodb connection in VBScript in UFT.

When working as Test automation Engineer, you will come across many use cases around excel files. There can be some scenarios where you have to read data from excel at runtime and you can do this by treating th excel workbook as a database. VBScript provides ADODB object using which you can establish a connection to the excel file as a database and read the data. You can also pass the normal sql queries to find the data in it. Just as you would if working with a database.

This page has list of all the functions which you can use to read an excel using adodb connection with the workbook.

Read excel using adodb connection using VBscript : Sample code
Msgbox GetDataFromDB("fullpath_of_excel_workbook.Xls", "Select * From [Deposits$]")

Function GetDataFromDB(StrFileName, StrSQLStatement)
  Dim ObjAdCon, ObjAdRs
  Set ObjAdCon = CreateObject("ADODB.Connection")
  ObjAdCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & StrFileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
  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                                  'Check The Field Names
  While ObjAdRs.EOF=False
    For I=0 To ObjAdRs.Fields.Count-1
      Msgbox ObjAdRs.Fields(I)
    Next
    ObjAdRs.MoveNext
  Wend
  If Err0 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
How to Read Excel using adodb connection using VBScript- Sample code to get 1st cell data from Database connection
' Get data from Excel as a Database
' Get Data from excel by using Database connection

Function GetDataFromDB(strFileName, strSQLStatement)
  Dim objAdCon, objAdRs
  Set objAdCon = CreateObject("ADODB.Connection")
  objAdCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="&strFileName&";Extended Properties='Excel 12.0 Xml;HDR=YES';"

  Set objAdRs =objAdCon.Execute (strSQLStatement)
  GetDataFromDB= objAdRs.Fields.Item(1)
  objAdRs.Close
  Set objAdRs.ActiveConnection = Nothing
  objAdCon.Close
  Set objAdCon = Nothing
End Function

Discover more from Automation Script

Subscribe to get the latest posts sent to your email.

Related Posts

How to add APACHE POI in Selenium Project – 2 Simple Steps

APACHE POI is the most used library for working with Excel workbooks in Selenium Automation projects. There are two ways you can add these jars to your automation project. One way is through the POM.xml in your maven project and the other way is by downloading the jars directly from Apache website and adding them in build path of your automation project.