the code below will create a comma delimited string. You can use split or
text to columns to seperae fields.

Option Explicit

Sub TestOne()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Long
Dim myrow As String

ThisWorkbook.Sheets(3).Cells.ClearContents


cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
ThisWorkbook.Path & "\Test.mdb"
rs.Open "Select * from [Case]", cn



Do While Not rs.EOF
For i = 1 To 10
If rs.EOF = True Then
Exit For
End If

myrow = rs.GetString(StringFormat:=adClipString, _
numrows:=1, ColumnDelimeter:=",")
ThisWorkbook.Sheets(3).Cells(i, 1) = myrow
rs.MoveNext
Next i
Loop

rs.Close
cn.Close

End Sub


"Varne" wrote:

Hi!

The below given Macro reads data from Access on a cell by cell basis. Can
someone make adjustments on it make it copy a chunk of field 1. It is like
for example copying records(2,3,4) in Access and paste onto cells(1,1) in
Excel.

Thank You.

----------------------------------------------------------------

Option Explicit
------------------------------------------------------------------
Sub TestOne()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Long

ThisWorkbook.Sheets(3).Cells.ClearContents

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
ThisWorkbook.Path & "\Test.mdb"
rs.Open "Select * from [Case]", cn

Do While Not rs.EOF
For i = 1 To 10
If rs.EOF = True Then
Exit For
End If
ThisWorkbook.Sheets(3).Cells(i, 1).Value = rs.Fields(2).Value
ThisWorkbook.Sheets(3).Cells(i, 2).Value = rs.Fields(3).Value
rs.MoveNext
Next i
Loop

rs.Close
cn.Close

End Sub