Adding a new row into an excel spreadsheet from access

[juddymarch]

Hi I have an access application that uses automation to pass out data from a query to an existing excel file.
The code works fine and I can export the data into the template, however I would like to add a new
row in the excel file every time a new record is added. I need to do this as I would like to keep some particular cells at the bottom of the sheet all the time. And the output of the query could range from 5 records to 800 records.

Code used:
Dim conDB As ADODB.Connection
Dim rs As ADODB.Recordset

Set conDB = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open "qryExcelMonthlyExport", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If Not rs.BOF And Not rs.EOF Then
iRow = cStartRow
rs.MoveFirst

Do Until rs.EOF
iFld = 0
lRecords = lRecords + 1
Me.Repaint
wks.Cells(iRow, 1) = rs.Fields("fldRegistrationNo")
wks.Cells(iRow, 2) = rs.Fields("fldSquadUnit")
wks.Cells(iRow, 4) = rs.Fields("fldMonthKmBusiness")
wks.Cells(iRow, 5) = rs.Fields("fldMonthKmPrivate")
iRow = iRow + 1
rs.MoveNext
Loop
wbk.Save
MsgBox "Total of " & lRecords & " rows processed.", , "Attention!"
Else
MsgBox "There are no records to export!", , "Attention!"
End If

Im sure its just a simple line of code to add the row but i cant seem to find what im after.
Any help appreciated
Thanks
Justin

 

[JerryKlmns]

Justin

>keep some particular cells at the bottom of the sheet all the time

Why don't you export again all the records along with those particular cells' values you want?

 

[juddymarch]

Sorry, the cells at the bottom contain formatting/merged cells. It would be much easier if I could just create a new row in the sheet at a specified point.
So the sheet can grow according to how many records there are.
Thanks
Justin

 

[bubba100]

Actually it would be easier to wipeout what is there then format/merge/enter formula each time using VBA.