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

Linking an Excel Spreadsheet

 
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

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

 
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