Acess and excel

[Guest]

Hello,

I have a excel tabe with 60 columns. first 5 of these columns are data to be
filled and rest is calculated automatically using formula in excel. I expect
in due course of time there will be 1000 rows filled (at present only 200
are filled). so all my 1000 rows (except the first five cells) are filled
with formula waiting to calculate once the first few data is entered.

I thought it may be good idea to creat a form in acess so taht i can enter
the data in my excel table by filling the form in acess.

I created the data entry form. linked with the excel table. till here it
worked fine. But, when I fill the form the data in excel table ge

ts entered into 1001th row which has no formula to calculate. instead of
201th (next empty row) row which I wanted.

Is there any way I can force the data enetrd using acess form to get entered
into next empty line in excel table.

Please help,

Thanks

 

[Marc]

worked fine. But, when I fill the form the data in excel table ge

ts entered into 1001th row which has no formula to calculate. instead of
201th (next empty row) row which I wanted.

Is there any way I can force the data enetrd using acess form to get
entered
into next empty line in excel table.

Please help,

Thanks

Before you start putting the data into the Excel sheet have a routine that
goes through checking which rows are entered.

nextRow = 0
bnFound = False
For x = 1 to 1000
If IsNumeric(objXLSheet.Cells(1, x).Value) Then
If objXLSheet.Cells(1, x).Value = 0 Then
bnFound = True
nextRow = x
x = 1000
end if
else
bnFound = True
x = 1000
nextRow = x
end if
next x

objXLSheet.Cells(nextRow, 1).Value = ...

hth
Marc

 

[MacDermott]

Why not just use Data - Form to enter your data directly into Excel?

 

[Guest]

Yes I used the data entry form that I made in acess used to enter data
directly into excel table. My problem is the data gets enetered into excel
tabel but not where i want.

For example

First 200t data in my excel table was entered manually in excel. when I
created data entry form in acess I used this form to enter my 201th data
into the table. Now, instead of going to 201th row in the table it went to
1001th row where there is no formula calculate (rows between 200 and 1000
remained empty). Similarly my 202nd data went to 1002nd row instead of 202nd
row and so on.

I wanted the data to go to the next empty row.

Thanks

 

[Guest]

Being a novice user of acess could you please elaborate where should I put
that expression .

Thanks

 

[MacDermott]

I meant -
why not use Excel's built-in data entry form option?
You don't need to use Access at all for this...

 

[Marc]

Hi,

This should go infront of the code you currently have for putting the data
into the sheet.

HTH
Marc