VBA/Excel/Access/Word/Excel/Excel Recordset

Linking an Excel Spreadsheet

   <source lang="vb">

Sub Link_ExcelSheet()

  Dim rst As ADODB.Recordset
  DoCmd.TransferSpreadsheet acLink, _
      acSpreadsheetTypeExcel8, _
      "mySheet", _
      CurrentProject.Path & "\Regions.xls", _
      -1, _
      "Regions!A1:B15"
  Set rst = New ADODB.Recordset
  With rst
     .ActiveConnection = CurrentProject.Connection
     .CursorType = adOpenKeyset
     .LockType = adLockOptimistic
     .Open "mySheet", , , , adCmdTable
  End With
  Do Until rst.EOF
     Debug.Print rst.Fields(0).Value, rst.Fields(1).Value
     rst.MoveNext
  Loop
  rst.Close
  Set rst = Nothing

End Sub

</source>
   
  

Open the Excel worksheet, create a recordset with the data in the sheet, and then print it in the Immediate window.

   <source lang="vb">

Sub openWorksheet()

  Dim myConnection As New ADODB.Connection
  Dim myRecordset As ADODB.Recordset
  
  myConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=C:\myCustomers.xls;" & _
     "Extended Properties=Excel 8.0;"
     Set myRecordset = New ADODB.Recordset
     myRecordset.Open "customers", myConnection, , , adCmdTable
     Do Until myRecordset.EOF
        Debug.Print myRecordset("txtNumber"), myRecordset("txtBookPurchased")
        myRecordset.MoveNext
     Loop

End Sub

</source>