asked on

Export Access Recordset to Excel with Field Names

Want to export my recordset to an OPEN Excel spreadsheet (not saved). The code below does everything I want except adding the field names in the first row.

I know I could use the docmd.TransferSpreadSheet... blah, blah, blah and set the HasFieldNames = True, but this saves and does not open. Is there a way to incorporate the HasFieldNames = True in my code below??

Thanks!!

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Private Sub cmdExportAllToExcel_Click()
Dim sSQL As String
Dim objXL As Object
Dim objWB As Object
Dim rstFull As Recordset

Set rstFull = CurrentDb.OpenRecordset("rptRRDataALL")
Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.Workbooks.Add

objWB.Sheets(1).Range("A1").CopyFromRecordset rstFull
objXL.Visible = True

rstFull.Close
Set rstFull = Nothing

End Sub

Rey Obrero (Capricorn1)

try this

Private Sub cmdExportAllToExcel_Click()
Dim sSQL As String
Dim objXL As Object
Dim objWB As Object
Dim rstFull As Recordset
Dim intColumn As Integer
Dim fldLoop As DAO.Field

Set rstFull = CurrentDb.OpenRecordset("rptRRDataALL")
Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.Workbooks.Add

intColumn = 0

For Each fldLoop In rstFull.Fields
intColumn = intColumn + 1
.cells(1, intColumn) = fldLoop.Name
Next fldLoop

objWB.Sheets(1).Range("A2").CopyFromRecordset rstFull
objXL.Visible = True

rstFull.Close
Set rstFull = Nothing

End Sub

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kerryw60

ASKER

Great!

Thanks!!