Importing From Excel

  • Thread starter Thread starter Dave Ruhl
  • Start date Start date
D

Dave Ruhl

Hi, I'm using Access 2002 and I'd like to semi-automate a
process that imports data from an Excel file into a
temporary table (overwritten with each import). Once
imported, I will parse the data and append it to several
tables. (don't need help with this step)

What I'd like is for the user to just have to select the
Excel file from the Explorer/File window, and have the
rest of the import process automated by code. This is
what I need help with, can this be done ? Thanks...
 
You can easily open an ADO or DAO Recordset against Excel sheet. Once you
read the data from sheet into the recordset, you can do what ever you want
to. You do not even need a temporary table, if your goal is to transfer
imported data from worksheet to various tables. You do not need Excel
installed in this case: the data is read by JET engine into ADO/DAO
recordset

So, if you do it from inside Access, you will
1. place a button on a form
2. When the form is clicked, display openfile dialog to get the Excel file
name
3. compose ConnectionString of ADO (or DAO, if you prefer), it may look
like:
cnString="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

4. open a recordset
5. Loop through the recordset to process data (send to various tables)
 
Thank you Norman and Lynn, I'm going to try out both
ideas and I'm sure I'll be able to accomplish my goal.
Thanks!
 
Back
Top