Import Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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


John Nurick said:
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
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
Click to expand...
Click to expand...
 
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
SELECT
F1 As MyName
FROM
[Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$B97:B97]
;
Click to expand...
Click to expand...




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


John Nurick said:
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
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top