Extracting data from Excel to Access db table

[Guest]

Hi,

I have to export data from Excel spreadsheet into Access database table.

We receive Excel files weekly about sales and so on. There is a separate
small tool created with Excel already which is supposed to extract data from
these Excel workbooks containing desired numbers and other data. The data is
then expected to be added into a Access database file (.mdb) already
containing data from previous weeks.

Could someone help with the vb code needed to execute these steps? The code
should be included in this Excel extraction tool mentioned before.

The Excel workbook sent to us weekly has columns with these headers
including some data:
a b c d e f g h i j k l m n o p

The Access table includes these fields:
a c d e g h i k l n p

So I only need some of the fields inserted into the db. Some of the Excel
columns have same headers as in access table and some do not, the data is
still in same format.

Basically I need the code that extracts data from Excel file row by row
filtering only needed column values at the same time and adds them into the
database table? Finally the code stops when an empty row is reached. Am I on
the right track here?

Thanks in advance,

 

[Jeff Boyce]

Andy

Another approach might be to link to the Excel spreadsheet from within
Access, and use one/more queries to append the data to your Access tables.
This approach also gives you the opportunity to take what is probably
not-to-well-normalized data (in Excel) and append/update your
well-normalized Access tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for reply,

Unfortunately the situation is as I described earlier. The extraction and
adding must be done with this separate excel tool. There is a main user who
opens this tool and pushes the button on weekly basis to extract the data.
The file names of source workbooks also vary. Ah the joy and glory of getting
aboard in the middle of development cycle...