Append query result 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 (precisely appending
one row of data coming from the access query to a specific worksheet in an
Excel workbook EVERY DAY) . 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
the line <ActiveCell.Offset(1, 0).CopyFromRecordset rs> is reached. Can you
please tell me what's wrong with my syntax? If this code won't do what I want
to accomplish , can you please suggest a different syntax

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

 

[Guest]

Hi... I achive this buy looping through the records in the access recordset
and pushing the values into the excel rows.

ie
do until rs.EOF
for c = 0 to rs.Fields.Count-1
ws.cells(r,c+1) = rst.Field(c)
next c

r = r + 1

rs.MoveNext
loop

Rgds
M.

 

[Guest]

Thanks for your reply. I only have one row of record in the access query that
I want to append to the excel sheet. That means I want the code to loop
through the rows in Excel till finding an empty row and copy the access
recordset into it.