Excel Import Puzzle

[Leo]

Hi -

I have an excel spreadsheet that is used to collect
information in a format completely different than the
format my access table is in. Think of my spreadsheet as
an application form where the user fills out information
such as "Name, Address, Tel, Comments, Order Description"

I am an access database that houses the similar
information in 3 different tables.

Because manually transferring this data can be quite
tedious, I am trying to find a way to import the values
in each of these excel cells into specific fields in my
access table(s).

I understand that I could use 'Ranges' in my excel
spreadsheet and have my access database to recognize
these 'Ranges' to import. Is this true? Can this be done?
If so, please provide me with some direction to get
started on this...
Thanks!!

 

[Joe Fallon]

Yes.
Access can import named ranges.
But in this case I do not recommend that strategy.

My usual strategy is to import the whole sheet into a "staging table".
This table is essentially the exact same data except now it is Access
instead of Excel.

Then you write 3 queries to transfer the data to your 3 tables.
If this process is repeated many times you can easily code these steps so
they are repeatable.

1. Clear the staging table.
2. Import the spreadsheet.
3. Run the 3 queries.

 

[John Nurick]

Hi Leo,

It sounds as if you want to pull values out of individual cells in the
"application form" worksheet. One way to do this is with Automation.
There's sample code at http://www.mvps.org/access/modules/mdl0006.htm
and more on the Microsoft website and elsewhere on the internet.

You can then use recordset operations (or create and execute
single-record SQL append queries) to put the values into the appropriate
tables.