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 FunctionHow 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
