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

update a new row everytime I click a button
 

I do logging like this a lot. The way I do it is as follows.

1. In the workbook, on the worksheet where the info will be gathering, I
use a worksheet formula "=counta(A3:A9999)" in cell A1.
2. Row 2 is used for my column headings.
3. Row 3 through Row 9999 is used to collect the info.
4. In the code, I query the value of cell A1(which is the number of
records) and store it as MyRecords.
5. Then I generate a NextRow variable by the formula "NextRow=MyRecords+3".
"3" is the starting row when there are no records.
6. I append the new data like this.
Worksheet("Sheet1").Range("A"+Cstr(NextRow)).Value =the new column A
data
Worksheet("Sheet1").Range("B"+Cstr(NextRow)).Value =the new column B
data
Worksheet("Sheet1").Range("C"+Cstr(NextRow)).Value =the new column C
data

Cell A1 updates and the new data is always put on the next row.

Caution: do not do this....
Worksheet("Sheet1").Range("A"+Cstr(
Worksheet("Sheet1").Range("A1").Value+3)).Value=th e new column A data
Worksheet("Sheet1").Range("B"+Cstr(
Worksheet("Sheet1").Range("A1").Value+3)).Value=th e new column B data
Worksheet("Sheet1").Range("C"+Cstr(
Worksheet("Sheet1").Range("A1").Value+3)).Value=th e new column C data
This will start by putting data in the first cell correctly, but the value
of cell A1 updates right away and the data for columns B and C will be
shifted down one row.


Dennis



"sam" wrote in message
...
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