Inserting to an Access table from an Excel spreadsheet

Question

  I have an Excel table that I want to insert into an existing Access table.  I see that I can use the DoCmd.TransferSpreadsheet command to execute this.  Yet, what I want to do is read a row and then insert the columns that I want into the table.  Not all columns will be loaded into the table.  This command seems like it will insert all columns and this is not what I want to do.  I only want to load specific columns from the Excel table.  I think I need to name the columns in Excel the same name as the table columns.  Can someone point me to the best way that I can get this done?  Thanx in advance.

Jerry

Answer 1

I'd do it as follows:

1. Import the entire Excel table into your Access database, or link to it.
2. Create a query based on the imported or linked Excel table, and change it to an append query with the existing Access table as target.
3. Add the columns that you need to the query grid, and specify which columns they should append data to.
4. Execute the append query.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

If you need complete control on importing then you can forget about DoCmd.TransferSpreadsheet this command is all or nothing...

Alternative you could work with Excel Automation via VBA and get the data by reading cell by cell

https://www.access-programmers.co.uk/forums/showthread.php?t=248093

This would be slower but it would give complete control...like what to insert..validation..manipulation...insert to multiple tables according to criteria....etc