Importing MS Excel Spreadsheets into MS Access Repeatedly

  • Thread starter Thread starter Wenyonk
  • Start date Start date
W

Wenyonk

I have MS Excel spreadsheet data that I need to import
each day.

The layout of the data (number of colums and colum
headings are always the same). The number of rows vary
from day to day as well as the MS Excel file name.

I would like to automate this process either with VB Code
or a macro. I have heard of various ways to do this via
the TransferSpreadsheet command or creating a linked
table then importing the data.

The new spreadsheet data needs to be appended to the
exisiting table.

I would like the most simpleist method for the User to
complete this task each day. I think my preference is
not to have the user create a linked table every day?

Please help with examples of code if possible?
 
Hi Wenyonk,

The best approach depends on the contents of the spreadsheet.

1) Do the daily spreadsheets contain:
(a) all new data (i.e. none of the data in each day's spreadsheet
already exists in the Access table)?
(b) modified data (i.e. the data in the spreadsheet must update existing
records in Access)?
(c) a mixture of the two?

2) Are the values in the spreadsheet certain to be of the correct data
types for the Access fields? (In Excel there is nothing to stop the user
mixing text and numeric values in a single column, while in Access each
field has its own fixed data type.)

If the answer to 1 is (a) and to 2 is Yes, then it's simple. Use a
button on a form; in the button's Click event procedure, first use the
code at http://www.mvps.org.access/api/api0001.htm so the user can
specify the import file, and then use DoCmd.TransferSpreadsheet to
import the data to the Access table. (Normally, Access will
automatically import all and only the rows that have data in them.)

In other circumstances things are less simple. Sometimes you can link to
the spreadsheet (using TransferSpreadsheet) and then use an Append query
to move the data from the linked table to the Access table. With some
mixtures of data in the spreadsheet, linking won't work and you'll need
to import it to a temporary table and then use an append query.
Occasionally even more complicated techniques are needed for reliable
automated importing.
 
Back
Top