appending rows to excel file

[Guest]

hi all,
I want a button click event in a form to do the following:
Append the result of an access query to an excel file. The query is a
crosstab query, so PLEASE DO NOT SUGGEST an Append Query -I do not need
that-. I wrote the following codes that returned the error message "Select
Method of Range Class Failed" when it reached the syntax of copying the
recordset into EXCEL.

Please assist me to find out what's wrong with this part of codes.

Private Sub cmd_ChildrenCountCharts_Click()
Dim strxlfile As String
Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim rs As Recordset

strxlfile = "M:excel files\qry_ProgramTotals.xls"
Set db = CurrentDb
Set rs = db.OpenRecordset("qry_ProgramTotals", dbOpenSnapshot)

Set xlapp = CreateObject("excel.application")
With xlapp
..Visible = True
..WindowState = xlMinimized
End With
Set xlbook = xlapp.Workbooks.Open(strxlfile)
Set xlsheet = xlbook.Worksheets("Programs Total")
xlsheet.Cells.Range("a1").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(1, 0).CopyFromRecordset rs

Set xlapp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing
rs.Close
Set rs = Nothing

End Sub

 

[Andi Mayer]

hi all,
I want a button click event in a form to do the following:
Append the result of an access query to an excel file. The query is a
crosstab query, so PLEASE DO NOT SUGGEST an Append Query -I do not need
that-. I wrote the following codes that returned the error message "Select
Method of Range Class Failed" when it reached the syntax of copying the
recordset into EXCEL.

strxlfile = "M:excel files\qry_ProgramTotals.xls"

theres a typo (a \ is missing after the M but it will work mostly

ActiveCell.Offset(1, 0).CopyFromRecordset rs

you have to use a range, with select it doesn't work

 

[Guest]

Thanks, can you please be more specific, where should I use the range? What
the syntax should be when using a range?

 

[Andi Mayer]

Thanks, can you please be more specific, where should I use the range? What
the syntax should be when using a range?

sorry the ActiveCell.Offset(1, 0). is a range

I tried it with Office 2000 and it works

Maybe:
If the Recordset object contains fields with OLE objects in them, this
method fails