Use ADO to connect to the excel file using MDAC (Microsofts Data Access components)

'Read Excel File Using ADO
Public Function Read_Excel _
         (ByVal sFile _
          As String) As ADODB.Recordset

      On Error GoTo fix_err
      Dim rs As ADODB.Recordset
      Set rs = New ADODB.Recordset
      Dim sconn As String

      rs.CursorLocation = adUseClient
      rs.CursorType = adOpenKeyset
      rs.LockType = adLockBatchOptimistic

      sconn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & sFile
      rs.Open "SELECT * FROM [sheet1$]", sconn
      Set Read_Excel = rs
      Set rs = Nothing
      Exit Function
fix_err:
      Debug.Print Err.Description + " " + _
                  Err.Source, vbCritical, "Import"
      Err.Clear
End Function

I got this code from the FIRST hit of a Google search of these terms:

vb6 ado excel

Thats a hint for you!

If are a diehard ADO user. Here is the connection string for Excel

sXL = "c:\DaniWebExample.xls"
Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sXL & ";Extended Properties=Excel 8.0;Persist Security Info=False"
Cn.ConnectionTimeout = 40
Cn.Open


The rest is the usual ADO recordset retrieving technique

This question has already been answered. Start a new discussion instead.