Exporting curretn recordset to Excel - only working first time!

[Louverril]

The code below works the first time it is run - the displayed records are
output to a excel spreadsheet called book1 with the worksheet named as it
should be.
So you open a form and click the button on the ribbon referencing this code
and all the displayed records are transferred to a spreadsheet.

However if you then try to run the code again without first closing and
reopening the form you get a blank sheet.

Any ideas?

Also any idea how to make this work with an open table as well as a form. If
I run the form on an opne table I get the error "2475 You entered an
expression that requires a form to be the active window". Followed by an
automation error "the object invoked had disconnected from its clients". And
error 91 object with etc... I realise this is because I have used ActiveForm.


'revised for late binding
'define variables
Dim xlApp As Object
Dim xlWorkbook As Object

'create the excel application object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

'create a new workbook
Set xlWorkbook = xlApp.Workbooks.Add

'define variables
Dim objRST As Recordset
Dim strSheetname As String

'create the recordset
Set objRST = Screen.ActiveForm.Recordsetclone

'create a sheet name - must be 30 characters or less
strSheetname = "ANE Business System Export"

'copy data from the recordset to the cells
Dim xlsheet As Object

Set xlsheet = xlWorkbook.Sheets(1)
With xlsheet
..Cells.CopyFromRecordset objRST
..Name = strSheetname
End With

'clean up all variables
Set objRST = Nothing
Set xlsheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing

 

[Jon Lewis]

Adding:

objRST.MoveLast
objRST.MoveFirst

immediately after Set objRST = Screen.ActiveForm.RecordsetClone should solve
the problem.

For a table use:
Set objRST = Screen.ActiveDatasheet.RecordsetClone

Note: It's generally not a good idea to expose tables in the UI of your
database application

Jon