Fill Excel table with Access data

[Stevenn]

How can I fill an Excel table with data from my Access database?

I know how to connect to the database and defining my recordset, but don't know how to handle the tables.

Can I do something like ListObjects("Table1").Cells.objRecordset.GetRows()?

 

[gaj104]

To get the fields you need to loop through their names. Then use CopyFromRecordset to "paste" the recordset.

i.e.

For i = 0 To objRecordset.RecordCount - 1

    Cells(1, i + 1).Value = objRecordset.Fields(i).Name

Next i

Range("A2").CopyFromRecordset objRecordset

Hope it helps

 

[Stevenn]

To get the fields you need to loop through their names. Then use CopyFromRecordset to "paste" the recordset.

i.e.

For i = 0 To objRecordset.RecordCount - 1
 
    Cells(1, i + 1).Value = objRecordset.Fields(i).Name
 
Next i
 
Range("A2").CopyFromRecordset objRecordset

Hope it helps

Thanks for the answer. Sorry if my post was not understandable. I know how to insert data in my sheet, but i want to insert in a specific table under the right column names

 

[ravi4ever]

You should update your Select query on the basis of Excel template and fix their column names and change your desitionation cell ( Range("XX")).. don't populate the Fields name again.

Query 1.

Range("A2").CopyFromRecordset objRecordset

Query 2
Range("AA2").CopyFromRecordset objRecordset

Query 3
Range("BA2").CopyFromRecordset objRecordset

And if you really don't want the update the query then use a Match function to check for for the Filelds name in a column header in Excel and paste the data of that column below the column name(through a loop for each item in that column or some method would be there to paste only one column).