Export Access record into Excel

[Marko24]

How do I export Access record (in DAO) into an Excel worksheet?

Does anyone have an sample code for exporting a record into a Excel worksheet? I have no idea how to do this. Can anyone help?

 

[Hasu]

Hi Marko,

try this,

DoCmd.OutputTo acOutputTable, "mytest", acFormatXLS, CurrentProject.Path & "\mytestfile.xls"



Hope this helps...
Hasu
(Trust on someone, someone will trust you!)

 

[Marko24]

I have tried that before, but this method exports the whole table into the excel. I only would like to export a selected number of fields from a selected (bookmarked) record into excel.

 

[Hasu]

Hey Marko,

You can create a Make-Table query to move selected records, this will create temp table and you can specify nos of fields, criteria in where clause.

after creating query use below code to run and export into excel.

DoCmd.SetWarnings (False)
DoCmd.OpenQuery "qrySelectedRecords"
DoCmd.OutputTo acOutputTable, "tblTest", acFormatXLS, CurrentProject.Path & "\mytestfile.xls"
DoCmd.DeleteObject acTable, "tblTest"
DoCmd.SetWarnings (True)



Hope this helps...
Hasu
(Trust on someone, someone will trust you!)

 

[krinid]

You can do this from Excel:

' RS is recordset filled with data from a query
numRecs = activesheet.range("a1&quot.CopyFromRecordset(RS)
' must catch return value or doesn't work