Hi

Thanks for replying.

The macro draws on the Access cell row 1 in field 1 throughout the loop.

And h is no string. It is the variable for column in Excel so just like 'i'
coming in both sides of the equation h also should come in both sides. Where
and How?

rs.Fields takes care of 'i' but what would take care of 'h'. 'rs.Records'
does not work. In Excel 'Cells' takes care of both.

I missed to declare 'h' last time. Revised codes;

Sub TestOne()

On Error GoTo a

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

For h = 1 To 5
For i = 1 To 5
If rs.EOF = True Then
Exit Sub
End If
ThisWorkbook.Sheets(3).Cells(i, h).Value = rs.Fields(i).Value
Next i
Next h

rs.Close
cn.Close

a:
Exit Sub
End Sub


Thank You.

"Mike" wrote:

Sub TestOne()

On Error GoTo a
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

For i = 1 To 5
If rs.EOF = True Then
Exit Sub
End If
'ThisWorkbook.Sheets(3).Cells(i, "h").Value = _
rs.Fields(i).Value '***the problem line
ThisWorkbook.Sheets(3).Cells(i, "h").Value = _
rs.Fields(1).Value '***the problem line
Next i
rs.Close
cn.Close

Exit Sub
a:

End Sub

"Varne" wrote:

Hi

Could you please help in completing the loop;

Sub TestOne()

On Error GoTo a
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


For i = 1 To 5
If rs.EOF = True Then
Exit Sub
End If
ThisWorkbook.Sheets(3).Cells(h, i).Value = rs.Fields(i).Value ***
the problem line
Next i
rs.Close
cn.Close

a:
Exit Sub
End Sub


"Joel" wrote:

Your question can have a few different answers. You can put code in a loop
to extract the data. You need to move down one row at a time and then read
the data from the field(s) you want.

You can extract an entire table if you want depending on how you have yur
database setup. Excel can read predefined tables and query from Access.

One thing you can do is in excel start the macro recorder. then perform a
query by using the worksheet menu Data - Import External data - New Database
Query. Select Access as the database and browse for the MDB file. Then
select the option you want to import into excel. You can then use the
recorded macro in your own macro. You can also modify the recorded macro as
required.

"Varne" wrote:

Hi

The following can extract data from one row in an Access table;

(Connection and Recordset declared)

ThisWorkbook.Sheets(3).Cells(h, i).Value = rs.Fields(i).Value

But does Access has something similar to Cells(h,i) to enable extracting
data of a whole table using a 'For Loop'.

Can someone help?

Thanks.