Eporting List's values to excel

[bugsy]

Hi everybody

I am having a terrble problem exporting list's values to Excel.

This how I am doing it

Private Sub cmdFailedToExcel_Click()


Dim tSQL As String

Dim sql As String

Dim TradeDate
Dim fld As DAO.Field
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim rng As Excel.Range

Set xl = New Excel.Application

Set wb = xl.Workbooks.Add


wb.Worksheets(1).Range("A1").CopyFromRecordset Me.lstFailed.Recordset, wb.Worksheets(1).Rows.Count - 1

wb.Worksheets(1).Range("A1").EntireRow.Insert

Set rng = wb.Worksheets(1).Range("A1")



For Each fld In Me.lstFailed.Recordset.Fields

    rng.Value = fld.Name
    Set rng = rng.Offset(, 1)
Next fld

wb.Worksheets(1).UsedRange.EntireColumn.AutoFit

xl.Visible = True
End Sub

It works the first time
But not the second

Upon investigation it looks like recordset is going to the end, and then doesn't go back to beginning

I've added

lstFailed.Recordset.MoveFirst

but it doesn't seem to help

 

[krc777]

I have used this and it worked well:

Access to Xcel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "exporthist", "c:\exporthist.xls"

HTH

 

[bugsy]

Hey krc

What you are showing will copy table/query.
I am looking at exporting values from a list in a form