inserting data from excel into access...

[vassilis]

hello everybody

i have something in my mind that im trying to do but i dont know how...
there is a Excel table using many functions in its 4 fields...The last row of
this excel sheet represents the 4 totals (currency euro) of all functions
of the above...that im trying to do is this row to be the record of a form
(or a table) into my access db but how i can make the insertion of data from
the excel to access to became automaticly (somewhere in google i read about
the copy paste solution but it would be great if it could be in auto).
if anyone could help i would be greatfull.
thanks....

 

[John Nurick]

Hi Vassilis,

Here's one approach:

1) In Excel, select the 4 cells and use Insert|Range|Name to create a
named range including these cells. Let's call it "Totals". Close and
save the worksheet.

2) In Access create a new query, switch to SQL view and type something
like this:

SELECT F1 AS AA, F2 AS BB, F3 AS CC, F4 AS DD
FROM [Excel 8.0;HDR=No;Database=C:\Folder\Book.xls].Totals
;

Replace AA, BB etc. with the actual names you want for your four fields,
and "C:\Folder\Book.xls" with the actual filespec.

3) When the query is working, you can either use it as the datasource
for another query or form or something, or you can convert it into an
append query to append this record to an Access table:

INSERT INTO MyTable (AA, BB, CC, DD)
SELECT F1 AS AA, F2 AS BB, F3 AS CC, F4 AS DD
FROM [Excel 8.0;HDR=No;Database=C:\Folder\Book.xls].Totals
;

 

[vassilis]

thanks John ....
ill try to follow your instructions ....

Hi Vassilis,

Here's one approach:

1) In Excel, select the 4 cells and use Insert|Range|Name to create a
named range including these cells. Let's call it "Totals". Close and
save the worksheet.

2) In Access create a new query, switch to SQL view and type something
like this:

SELECT F1 AS AA, F2 AS BB, F3 AS CC, F4 AS DD
FROM [Excel 8.0;HDR=No;Database=C:\Folder\Book.xls].Totals
;

Replace AA, BB etc. with the actual names you want for your four fields,
and "C:\Folder\Book.xls" with the actual filespec.

3) When the query is working, you can either use it as the datasource
for another query or form or something, or you can convert it into an
append query to append this record to an Access table:

INSERT INTO MyTable (AA, BB, CC, DD)
SELECT F1 AS AA, F2 AS BB, F3 AS CC, F4 AS DD
FROM [Excel 8.0;HDR=No;Database=C:\Folder\Book.xls].Totals
;

hello everybody

i have something in my mind that im trying to do but i dont know how...
there is a Excel table using many functions in its 4 fields...The last row of
this excel sheet represents the 4 totals (currency euro) of all functions
of the above...that im trying to do is this row to be the record of a form
(or a table) into my access db but how i can make the insertion of data from
the excel to access to became automaticly (somewhere in google i read about
the copy paste solution but it would be great if it could be in auto).
if anyone could help i would be greatfull.
thanks....