Linking an Excel Spreadsheet : Excel Recordset « Excel « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Excel
3. 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

 

Related examples in the same category