Using Excel for data entry

[Ric Sinclair via AccessMonster.com]

I would like to use an Excel worksheet as a data entry form for the sales
quantity budget: week number in the columns, products in the rows (last
years quantity by week in the following row for reference). Each worksheet
would be a separate customer group.

I want to be able to import this into Access with the following fields:
customer group; product; week; LY Qty; TY Qty.

Can I automate the conversion for importing to Access or does it have to be
a long process of cut and paste? (we have 800 products x 40 customer groups
x 52 weeks x TY/LY= 3.3 million entries).

Thanks

 

[John Nurick]

Hi Ric,

Yes, this can be automated, though it will take a moderate knowledge of
DAO or ADO as well as VBA and the Excel object model.

The biggest challenge in this sort of thing is is data validation in
Excel, to ensure that the data entered can import cleanly into the
database.

You also need to make certain that every row can be uniquely identified
by its contents alone without reference to the adjacent rows in the
worksheet. So if you have the ProductID or name in column A, the cells
of column B should contain the year to which each row refers.

 

[Ric Sinclair via AccessMonster.com]

John, thanks for the reply.