Import Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cathi - 30 Nov 2004 00:55 GMT

I have several excel workbooks, I need to import data from specific cells
(not the whole worksheet) into specific tables in Access 2000. I have the
import down as long as I just import a range, however a range is not what i
need. I need to be able to pick certain cells and import them into specific
fields in the access table. This has to be done with mutliple workbooks.

I have looked in several locations and can not find the code that I need or
that works for me. ANY help in figuring out the most optimum way of doing
this would be so greatly appreicated.

Thanks
Cathi

Reply to this Message

Alex Ivanov - 30 Nov 2004 05:18 GMT

Try to link required ranges or whole sheet to you Access database and then
run update queries based on that linked tables and those you want to update.

Signature

Please reply to NG only. This email is not monitored.
Alex.

>I have several excel workbooks, I need to import data from specific cells
> (not the whole worksheet) into specific tables in Access 2000. I have the
[quoted text clipped - 11 lines]
> Thanks
> Cathi

Reply to this Message

John Nurick - 30 Nov 2004 06:18 GMT

Hi Cathi,

If you know the cell references or range names you can use them to build
SQL append or update statements, e.g.

INSERT INTO
MyTable
SELECT
F1 As MyName
FROM
[Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$B97:B97]
;

IMO this is the simplest approach but there are other possibilities
depending on just what you need to achieve.

>I have several excel workbooks, I need to import data from specific cells
>(not the whole worksheet) into specific tables in Access 2000. I have the
[quoted text clipped - 8 lines]
>Thanks
>Cathi

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Cathi - 30 Nov 2004 15:37 GMT

John,

Yes, that will work perfect. Don't want links as I need a more stable setup
than that. However, one quickie question and this is where I was having the
problem......

Exactly how do I designate which field in the table it imports to? I have
been trying

INSERT INTO mytable (field1, field2, etc) but I keep getting an error
looking for end of statement. What am I doing wrong?

Thanks a lot for the help
Cathi

> Hi Cathi,
>
[quoted text clipped - 29 lines]
>
> Please respond in the newgroup and not by email.

Reply to this Message

John Nurick - 30 Nov 2004 19:33 GMT

Cathi,

You can specify the destination field either by providing a field list:

INSERT INTO MyTable( FieldName )
SELECT F1
FROM [Excel 8.0;HDR=No;database=C:\Temp\AC.xls;].[Smith$A4:A4];

in which case each field in the list is matched with the corresponding
field in the SELECT clause, or else by aliasing the field(s) in the
SELECT clause to the same name(s) as in the destination table:

INSERT INTO MyTable
SELECT F1 AS FieldName
FROM ...

If you want to set the values of other fields, include them like this:

INSERT INTO MyTable( FieldName, AnotherField )
SELECT F1, "FOO" AS F2
FROM ...
or this:

INSERT INTO MyTable
SELECT F1 AS FieldName, "FOO" AS AnotherField
FROM ...

If you use field lists, there must be the same number of fields in the
INSERT INTO ( ... ) as in the SELECT ... .

>> INSERT INTO
>> MyTable
[quoted text clipped - 3 lines]
>> [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$B97:B97]
>> ;

>John,
>
[quoted text clipped - 44 lines]
>>
>> Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message