Change Field Type When Importing Excel into Access

[slowmike]

I am trying to import an Excel spreadsheet into Access, but I cannot change the field types that Access automatically assigns to each field. Apparently if the first few records are populated with numbers, it calls that field a number, but if there are alpha characters in that field for subsequent records, it chokes. Is there a way I can override this without saving the sheet as text and importing that? There are quite a few sheets I need to import, so saving them as text would be a major PITA.

 

[SkipVought]

Hi,

1. Sort the sheet so that text items are first

2. format ALL cells in that column as TEXT


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[pbrodsky]

Skip
I just went through that. Didn't work - even after re-entering the 1st 15 or so values so that they were numeric-looking text values.
I ended up creating an extra column with a formula converting everything to text
(=if(isnumber(a1),text(a1,"0"),a1) or something like that.

New column imported correctly as text.

 

[slowmike]

So I guess there is no way to change the automatic field type assignments without going in to each individual sheet. Shoot. I guess that's why they pay me the huge coin. Thanks for your quick responses.

 

[pbrodsky]

There is always the possibility of writing a VBA routine using ADO or DAO. Not that much work if this is very repetitive, but probably too much if its only an occasional task.

 

[slowmike]

Well, I have almost 100 files to go through now. After that, it will be a weekly routine, but I would like to get it as automated as possible. I was thinking about writing a code that opens an Excel sheet that opens the file and saves it as text, but if you don't think that would work or have a better solution, I'm all ears.

 

[pbrodsky]

If you go into the VBA editor and look up the addnew method, there are some complete code examples of adding records to a table using VBA and DAO.

 

[pbrodsky]

Note: you could run this code either from Excel or from Access, with only minor changes.

 

[slowmike]

I noticed that if I import the sheet without using the field names provided in the sheet, Access calls everything text and does not choke on fields with alpha/numeric mixes. I can then assign field names and types in Access. All I have to do then is pray (or add code to verify) that the field order does not change. Does this make sense to you or is the AddNew method more reliable?