Rodney has attended:
Access Introduction course
Access Intermediate course
Access Advanced course
Import data from Excel/Csv file
Good Afternoon,
Please can you advise if it is possible to be able to set-up a macro that import data (From excel) from a designated directory.
I.e. if my list of units is saved in an excel spreadsheet, and is updated in Excel on a regular basis, and I would like access to just pick up this data and update the table. The headings will also been the same, just further data is added.
Thanks
Rodney
RE: Import data from Excel/Csv file
Hi Rodney
Thanks for your question. Whilst setting up macros may achieve the result you are looking for you might find it easier to use a built-in Access feature instead.
With your database open go to the File menu and choose Get External Data then choose Link Tables. From the dialogue box that appears navigate through to your Excel workbook, select it and click on OK.
The import spreadsheet dialogue box appears. Choose the sheet that contains the table of data you are interested in (or choose named ranges if you created a range for your data) and then click Next.
Access asks if the first row contains column headings - select the appropriate option. Click Next to decide where the information should be stored in Access (in a new or exisiting table). The remaining steps of the wizard take you through naming the fields of data, optionally adding a primary key and finally naming your table.
In your list of tables you should see the icon for your new table has an arrow beside it indicating that the data in the table is in fact stored outside of the database.
If your records change in Excel then the data will be automatically updated in Access (and vice versa)
Hope this helps Rodney - do let us know if you have any further questions.
Kind regards,
Andrew
