update a new row everytime I click a button
 

Hi All,

How can I loop through excel rows by clicking a button. I want to populate a
new row in excel every time I click a button.

For eg: I Click a button that I created on access form, on clicking this
button will open a dialog window where we can select the excel file(template)
then we launch excel file then --Populate it -- Save it with different
name..

I want to keep this excel file open and keep on updating the sheet with data
in the next empty row with records from access by clicking that button...
Is there a way to loop it like this? and keep updating the sheet with data
in next row?
Here is the part of code for looping through new rows. But it doesnt work as
I want it to.


Dim r as long

Set wst = Worksheets("sheet1")

With wst
..Range("A1").Value = Me.City.Value

End With

r = r + 1

FName = "C:\My Documents\" _
& "Address" & ".xls"

wst.SaveAs FileName:=FName

Hope I made it clear.

Thanks in Advance