Import, Export, Append, Overwrite Access table from Excel

Question

Need is to use an Excel spreadsheet for end user to enter data and import/export to Access 2007 as follows:

1. On a form import into existing Access table the data from an Excel worksheet by clicking a control on the form.  (Doing it manually with a wizard is known and not wanted.) Options need to be REPLACE entire existing Access table or APPEND to entire existing Access table.

2.  On a form Export the table to Excel with column headers by clicking on a control on the form.

Thanks in advance for direction on this.

Answer 1

Those are some very broad questions. 

You might take a look at Ken Snell's site: http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm.  Perhaps some of his samples will help.

---

-- Roger Carlson

MS Access MVP 2006-2010

www.rogersaccesslibrary.com

Answer 2

Broad questions, yes.  However, you link to the source is exactly what I needed!  Thanks.

Answer 3

Linking to spreadsheet is a good idea, but does not work in this environment.  The spreadsheet source may be emailed around or stored in a not-connected location.  Seems that the act of re-linking might be a bit cumbersome.  ALso, sometimes we want to append from spreadsheet, sometimes we want to replace the entire table.  Spreadsheet import is for situations where someone says, "I don't want to type all that in-- here it is in a spreadsheet."  ALso, ultimately we want a very portable .accdb file with nothing external.

Answer 4

Why not just link to the spreadsheet? Any changes made in the workbook will then be reflected in the linked table?

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.spaces.live.com/blog Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries