excel access macro

D

David Susseles

Hello,

I am looking to learn how to build a macro / procedure to copy data in excel
and paste into an ms acess table.

any help would be appreciated.

Thanks.
 
J

Jamie Collins

David Susseles said:
I am looking to learn how to build a macro / procedure to copy data in excel
and paste into an ms acess table.
Click to expand...

Wrong metal model <g>. Think 'update data in my Jet database table
using data in my Excel workbook'. Here's some sample code:

INSERT INTO
[Database=C:\MyJetDB.mdb.].MyTable
(MyTextCol)
SELECT
MyExcelCol AS MyTextCol
FROM
[Excel 8.0;HDR=YES;Database=C:\MyJetDB.xls.].[MyExcelTable]
;

You will need to find a suitable 'container' to execute the query e.g.
a stored query in the database, MS Query sql window in Excel, ADO in
code, etc. The above will work for any Jet connection e.g. to an
arbitary or non-existent workbook. If you are using a connection to
either the .mdb or the .xls, you can omit the connection string from
the sql text.

You need to ensure the Excel column is 'seen' by Jet as the correct
data type. For details, see:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

Jamie.

--
 
Top