Using ADO connection & SQL Query to extract specific rows from an excel sheet.

You can use plain excel script or ADO. If you need to extract all the information from the excel sheet than plain old excel script would do fine, but if you need to extract some specific information then ADO can be used as below: The below code runs the query which shows only the Failed rows in a message box.

The excel file as below is located at C:\sac.xls



below two lines determine how (and if) a recordset can be updated.
'Recordsets can be set to read-only, or they can be configured
'to allow updates. For most scripts, the LockType can be set to
'adLockOptimistic (value = 3). With this setting, the record being
'edited is not locked (that is, no restrictions are placed on another
'user accessing that record) until you call the Update method.

'adCmdText indicates that the command is a SQLstatement
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

'below just remember to give the location of your xls file
'HDR=Yes indicates that our spreadsheet has a header row and
'the provider will not include the first row of the cell range (which may
'be a header row) in the RecordSet.

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=C:\sac.xls;" & _
       "Extended Properties=""Excel 8.0;HDR=Yes;"";"

objRecordset.Open "Select * FROM [Sheet1$] where Result = 'Fail'", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
        a= objRecordset.Fields.Item("Scenario No")
        b=objRecordset.Fields.Item("Scenario Text")
        c=objRecordset.Fields.Item("Result")
        msgbox (a &" "&b&" "& c)
        objRecordset.MoveNext
Loop