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
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
"TS" <(E-Mail Removed)> wrote in message
news:11EA6F7B-89B5-4147-B3E7-(E-Mail Removed)...
> 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()
>> 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
>
>
>
> --
> TS
|
Similar Threads
