Excel Import Puzzle

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Leo - 01 Sep 2004 22:15 GMT

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!!

Reply to this Message

Joe Fallon - 02 Sep 2004 01:56 GMT

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.

Signature

Joe Fallon
Access MVP

> Hi -
>
[quoted text clipped - 18 lines]
> started on this...
> Thanks!!

Reply to this Message

John Nurick - 02 Sep 2004 07:48 GMT

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.

>Hi -
>
[quoted text clipped - 18 lines]
>started on this...
>Thanks!!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message