Still Not Working- Importing Excel Into Access

I tried posting this before, but I have not seen it posted yet, so I will post again.


I am trying to import an excel sheet into access.
I have searched the threads, and found similar problems, but none of the answers are helping.

The problem:
I use a macro to transfer the spreadsheet (Transferspeadsheet)

I have a column in excel that contains mostly numeric entries, but sometimes it will have alphanumeric entries.

Access will see the numeric entries and assume this is a numeric field- and then null my alphanumeric entries, and I will get importerror tables after the fact.

Many times the first 10 rows are completely numeric, but the 20th row is alpha numeric. If the alphanumeric row is closer to the top- no errors.

I have 60 spreadsheets to load every morning. Switching to access for all users would be a godsend, but also a fantasy in my environment. I have to find a work around for this. The users will always be using excel to enter this data.



I have tried:

1) Changing excel columns format to text.

Result: Access doesn't care. Sees the field as numeric- import errors on alphanumeric entries

2.) Linking to table

Result: if the first rows are numeric, when I scroll down to the alphanumeric entry it states "error" when I view the linked table in Access. (Access will not allow me to change the field properties of the linked table)

3.) Making a table in access with the "correct" feilds and exporting to excel. Having the users use that version.

Same result- import errors.



There is one thing I have not tried, and I am ready to try anything:
Somehow exporting excel to text and then importing the text.

HOWEVER: I read this as a solution someone has tried in this forum- but he had problems with date entries. (They didn't convert very well, I am assuming).

I have date entries in my spreadsheets, so I don't want to go there. Besides, I don't know how to automate the export of 60 spreadsheets to text.


I asked someone else, he suggested an "import map".

Did help search, Internet word search- nada.

Is there some sort of import template that can be used to force access to understand that I want a particular field as a text field?