appending rows to excel file

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
Click to expand...
strxlfile = "M:excel files\qry_ProgramTotals.xls"
Click to expand...
theres a typo (a \ is missing after the M:) but it will work mostly

ActiveCell.Offset(1, 0).CopyFromRecordset rs
Click to expand...

you have to use a range, with select it doesn't work
 
Thanks, can you please be more specific, where should I use the range? What
the syntax should be when using a range?
 
Thanks, can you please be more specific, where should I use the range? What
the syntax should be when using a range?
Click to expand...
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
 
Back
Top