Importing info from excel

B

bagnallc

Hi all,

Some advice please

Im currently setting up a database using a large amount of info (about
5 years worth of daily records) stored in excel, once its transferred
to Access i will then be breaking it down into tables (fifth form) and
running all sorts of queries etc.

Each future day though i need to transfer that days data into the same
Access database and tables etc so it is always up to date.

The data has to come from excel as i run a series of macros containing
formulaes etc there before it is correctly broken down.

What is the best way to set it up to transfer the info from excel over
to access each day?

Many thanks if anyone can help

Chris
 
K

Klatuu

There are a number of ways this can be done; however, this is my preferred way.

First, I use an Open Dialog box to allow the user to navigate to the desired
file. I don't like the behaviour and hassle of the ActiveX control. I use
the API you can find on this site:

http://www.mvps.org/access/api/api0001.htm

I create a table that has the structure of the incoming Excel worksheet,
defined with the data types I need.
First, I delete all the data in the table
Then I import the worksheet into the table using the TransferSpreadsheet
method.
Then I run the queries, code, or whatever to move the data into the
destination tables.
 
Top