Append CrossTab query result to EXCEL

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

Guest

hi all,
I only have one row of record in an access query that
I want to append to an 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. I posted this request before, but haven't gotten a correct
answer yet. The code I used is the following. Please help

Private Sub cmd_ChildrenCountCharts_Click()
 
You dont say what is wrong but it looks fine except you have offset by one
additional row as when you exit the loop you are already on an empty cell
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(1, 0).CopyFromRecordset rs
Click to expand...
you exit the loop on an empty cell then offset again
last line should be
ActiveCell.CopyFromRecordset rs

but you are better off not moving the cursor
ie
dim x as integer
x=0
Do Until IsEmpty(ActiveCell.Offset(x,0))
x=x+1
Loop
ActiveCell.Offset(x, 0).CopyFromRecordset rs

also you need to save the file before terminating excel

xlbook.save

rgds

Stephen
 
Thanks for your response. I made the change you suggested in the code and got
the error message: Select method of range class failed when the code reached
the following: xlsheet.Cells.Range("a1").Select

Please help!! I know I am very close to solve this thanks to you.
 
you are missing 1 line and strictly speaking you should activate the cell
(select is for a range)
xlsheet.activate

ie
xlsheet.Activate
xlsheet.Range("a1").Activate
Do Until IsEmpty(ActiveCell)
 
Thanks a million. It worked finally.

Stephen Haley said:
you are missing 1 line and strictly speaking you should activate the cell
(select is for a range)
xlsheet.activate

ie
xlsheet.Activate
xlsheet.Range("a1").Activate
Do Until IsEmpty(ActiveCell)
Click to expand...
 
Back
Top