How do I import Excel combo fields into Access?

[Guest]

I have a worksheet in Excel 2000 that has fields containing combo boxes.
When I import this worksheet into Access 2000, all the combo source
information is gone.
Is there a way around this or do I have to enter this information again in
design view?

Thanks for you help.

 

[John Nurick]

I'm not quite certain what you mean by "fields containing combo boxes".

If you're talking about data validation by list with the In Cell
Dropdown property selected, then there's no simple solution. The
Access/Jet import routine has no access to the data validation
properties.

It's possible - within limits - to write code to extract data validation
properties from an Excel range and create equivalent validation in
Access but it's not simple.

So it's probably simpler to work manually. If Source of the Excel
validation list is a range of cells, you can import it to a table and
set it up like an ordinary lookup table. If it's a comma-separated list,
you can copy and paste it into an Access combobox's RowSource property
(replacing the commas with semicolons - three cheers for Office
integration!).

 

[Guest]

Thanks John, this answers my question.

I'm not quite certain what you mean by "fields containing combo boxes".

If you're talking about data validation by list with the In Cell
Dropdown property selected, then there's no simple solution. The
Access/Jet import routine has no access to the data validation
properties.

It's possible - within limits - to write code to extract data validation
properties from an Excel range and create equivalent validation in
Access but it's not simple.

So it's probably simpler to work manually. If Source of the Excel
validation list is a range of cells, you can import it to a table and
set it up like an ordinary lookup table. If it's a comma-separated list,
you can copy and paste it into an Access combobox's RowSource property
(replacing the commas with semicolons - three cheers for Office
integration!).

 

[Antonella Savoldi]

Hi, I am writing from Italy, and I think to I have the same problem. I have
a field in Access that contains a multiple chioce menu. (the menu where you
have to put a flag for every options you need). I would like to know how to
import it into Access 2007 from Excel 2007. Is there any solution without
doing it manually? I have more than 1000 Records containing a value that must
be put under the field of multiple choice menu!! I would appreciate your
help..