Moving data from Excel to Access

[Fredek]

Hi guys,

I have created an "Archive" sheet which will hold 60 rows per day. The sheet is likely to get very big very quickly so I'm considering using Access DB as a way of storing the data. Is there a neat automated way of moving the archive data into an Access DB e.g. VBA?


Thanks!

 

[reberryjr]

Access has a native feature that allows you to import excel sheets in the External Data tab.

 

[hazel22]

You can follow those steps before moving data from excel to access:

Clean your data before you import
Before you import data into Access, in Excel it's a good idea to:


Convert cells that contain non-atomic data (that is, multiple values in one cell) to multiple columns. For example, a cell in a "Skills" column that contains multiple skill values, such as "C# programming," "VBA programming," and "Web design" should be broken out to separate columns that each contain only one skill value.


Use the TRIM command to remove leading, trailing, and multiple embedded spaces.


Remove non-printing characters.


Find and fix spelling and punctuation errors.


Remove duplicate rows or duplicate fields.


Ensure that columns of data do not contain mixed formats, especially numbers formatted as text or dates formatted as numbers.