Importing Excel Worksheet into Access

[netmog]

Hello All,

I am trying to import an Excel worksheet into Access. I made some simple formattting changes to my orignal worksheet (sorting, titles, etc) to prepare for import. Now my "prepared for import" version won't import. Any ideas?

I can e-mail a mini-version of the file if you like. E-mail me at moghad@nature.berkeley.edu

Thanks!

Jason in California...

 

[SydneyGeek]

A couple of things about Excel files destined for Access.
1. The layout must be a table, with field headers in row 1
2. I find it works better if I name the Excel data as a range, then import or link to that range -- avoids spurious Field18, Field19... in your Access table
3. Names / headings should NOT have punctuation -- a no-no in Access
4. Try setting the data type of all the columns before linking / importing.
5. If you are importing into an existing table, ensure that the field names, field orders, and data types match exactly.

HTH
Denis

 

[mdmilner]

As a suggestion for a quick fix.
Import the revised spreadsheet into Access and then compare the field formats in the new table to the old.

Or

Just use the new table - don't import it into an existing one.
-
There are some complex options.
Imports into Temporary Tables, and opening excel from Access and walking thru each cell, but that's sorta like using a sledgehammer to open a can of soda. Yeah it'll work, but it's overkill for some uses.

Mike