Share via

Import, Export, Append, Overwrite Access table from Excel

Anonymous
2010-12-07T14:18:16+00:00

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.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Anonymous
    2010-12-09T14:56:38+00:00

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

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2010-12-09T14:55:42+00:00

    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.

    Was this answer helpful?

    0 comments No comments
  3. ScottGem 68,810 Reputation points Volunteer Moderator
    2010-12-07T18:03:09+00:00

    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

    Was this answer helpful?

    0 comments No comments