Retrieve and parse data from access to excel 1

[lb1]

I have a table in access from which I want to retrieve the data. These data need to be inserted in specific cells in excel.
For example, the table "AA" has the following data:
AAAAA 25 32
BBBBB 35 25
etc..

I found a piece of code that will allow me to copy and paste all the table in an excel spreadsheet, but I cannot find a way to create a loop in the recordset.
Does anybody have a piece of code that I could use and modify to parse these data.
Thanks in advance.

 

[Voga]

Hi lb1. Here what you have to do: It's tested already and working. Let's assume you have command button named "cmdEnsertData"

Private Sub cmdEnsertData_Click()

Dim db As Database
Dim MydbName As String
Dim rs As Recordset
Dim i As Long
Dim NumberOfRecords As Long


MydbName = "YourDataBaseName" ' Your actual database name or its actual path (for example: "C:\My Documents\MyDatabases\YourDataBaseName&quot

Set db = OpenDatabase(MydbName)
Set rs = db.OpenRecordset("AA&quot'Where "AA" is your table you want to extract records from

NumberOfRecords = 2000 '"2000" here is any number of your records. Set this number more than your actual number just in case your database shall expand but never set it less than your existing number for unexpandable constant database.

If (rs.EOF And rs.BOF) = False Then 'If we want to start from the first record, BOF - first record, EOF - last one
rs.MoveFirst
For i = 1 To NumberOfRecords
If rs.EOF = True Then Exit For
Worksheets("MySheet&quot.Cells(i, 1) = rs("MyFirstField&quot
'"MySheet" is actual name of your Excel worksheet you want to ensert data into and "MyFirstField" is a name of your let's say first field, "MySecondField" - name of your second field and so on...
Worksheets("MySheet&quot.Cells(i, 2) = rs("MySecondField&quot
Worksheets("MySheet&quot.Cells(i, 3) = rs("MyThirdField&quot
Worksheets("MySheet&quot.Cells(i, 4) = rs("MyForthField&quot
Worksheets("MySheet&quot.Cells(i, 5) = rs("MyFifthField&quot
'and so on....
rs.MoveNext
Next i
End If
rs.Close
db.Close
End Sub
----------------------------------------------------------
But if you have many fields, want to save lines of code and typing time then use "nested For" Here is final code with "nested For" and without comments:

Private Sub cmdEnsertData_Click()

Dim db As Database
Dim MydbName As String
Dim rs As Recordset
Dim i As Long
Dim k As Integer
Dim NumberOfRecords As Long
Dim NumberOfFields As Integer



MydbName = "YourDataBaseName"
NumberOfFields = 20 'Your actual number of fields in DB
NumberOfRecords = 2000

Set db = OpenDatabase(MydbName)
Set rs = db.OpenRecordset("AA&quot

If (rs.EOF And rs.BOF) = False Then
rs.MoveFirst
For i = 1 To NumberOfRecords
If rs.EOF = True Then Exit For
For k = 1 to NumberOfFields
Worksheets("MySheet&quot.Cells(k, 1) = rs(k)
Next k
rs.MoveNext
Next i
End If
rs.Close
db.Close
End Sub
--------------------------------------------------------
Good luck.

 

[Voga]

Sorry there is a typo

The correct line of the last version of code is:

Worksheets("MySheet&quot.Cells(i, k) = rs(k)

 

[lb1]

Thanks for this very well documented info.

 

[DomThePom]

The code above will work but it is a bit slow - quicker to use the copyfromrecordset method of the range object as follows:

**********************************************
assume that you have defined an excel application, workbook and sheet (xlShtData), and you have a recordset (rst) that you want to copy to the sheet
***********************************************

'Write in and format the table / query field names
For icols = 0 To rst.Fields.Count - 1
xlshtData.Cells(1, icols + 1).Value = rst.Fields (icols).Name
Next
xlshtData.Range(xlshtData.Cells(1, 1), _
xlshtData.Cells(1, rst.Fields.Count)).Font.Bold = True
Set xlRng = xlshtData.Cells(2, 1)

'copy in the data
xlRng.CopyFromRecordset rst

*******************************************************