Importing Excel data into multiple tables

[dabbler]

Hi guys/gals,

Can anyone tell me the quickest way to do the following.

I have a contact access database which need a regular feed from maillist spreadsheets received from data suppliers, and I need to build an import wizard. The excel spreadsheet contains the following data headings:

Customer Name, Customer Address, Customer phone number, Contact Name.

There is already data in the access database, but I know need to add to the existing tables with these import feeds.

EXISTING ACCESS TABLES
====================
1. Customers

CUSTID (autonumber)
CUSTNAME
CUSTADDRESS
CUSTPHONE

2. Contacts

CONID (autonumber)
CONCUSTID (Foreign key to customers table)
CONNAME

PROBLEM
=======
As you can see from the excel spreadsheet there are multiple entities on one line, and I need to map that spreadsheet data into the tables as follows:



Excel column Database column

1. Customer Name Customers.CustName
2. Customer Address Customers.Custaddress
3. Customer phone number Customers.Custphone
4. Contact Name Contacts.Conname

Naturally the contact.conname needs to be populated with the concustid foreignkey.

Sorry for the long-windedness but hopefully all the information is there for you to help a stranded soul!

Thanks a lot...

 

[namliam]

You will have to do it by using VBA

Regards

 

[dabbler]

Does anyone have a copy of similar code?