Data type format problem with linked excel sheet

[Epsilon101]

Hi, i have got a few problems with an excel sheet, 1 of my columns has got mostly text in and then a few numbers in 2 of the cells further down, i need it to be set as Text so that it shows the number instead of the #NUM error in the field. I changed the column in excel to text and i re-linked it and when i open the table design in access it has this column set as text, yet in table data it has those two fields as #NUM error. Is this a problem with Access only looking at the first ten fields like in the wizard, or is there a way around it??

Any help would be great

---------------------------------------
KKhhhaaaaNNNN!!!

 

[ShellyL]

This is certainly not the most elegant solution but it works for me...
I assume that when you changed the column in Excel to text, you used the normal Excel formatting. What I've experienced is that this doesn't "really" change the format when it interacts with other applications.

Try this: Save your worksheet as a Tab Delimited text file. Then Open the .txt file in Excel, and make sure the field in question is defined as "text" as you are prompted by the Text Import wizard. Now, your data is "really" text, so you can Save As Excel again. Link to the new & improved Excel file, and you should be set.

 

[Epsilon101]

Unfortunately it needs to be running off the excel files, and i cant take time up changing to tab delimited and back everytime i update.

It has a column in the table whre the data is alphanumeric
Usually either a 3 letter word or a number.

Only a few are showing with a number, and access doesnt seem to care about them when linking them into it, and i cant use it straight from a txt file when linking because i use the first line as table headers and it doesnt let you when its in a .txt file. Does anyone know another way to do this quickly, or is it a problem with Access.

---------------------------------------
KKhhhaaaaNNNN!!!

 

[Epsilon101]

I have got it working off linked text files now nvm, such a bloody long winded way all because of 2 cells, had to save the excel sheet as txt, link to that text file, and it opens the wizard, select first row as header tab delimited and save specs for the format of the cells.

---------------------------------------
KKhhhaaaaNNNN!!!

 

[Molby]

Epsilon,

You can use the Text Function in Excel to change the numbers to text, that might save you having to work from text files.

 

[Epsilon101]

Yeah that was the problem, i changed the format of the column with the numbers and text in, to text and then linked it to Access table, and Access displays a #NUM error for the records with a number in that column.

I think it was because Access scans only the top 10 columns to figure out what format to give it. In this case only 2 of the cells had numbers in and they where not in first 10 rows.

I changed it to txt file comma delimited by mistake after the suggestion above and that didnt work, but i did it tab delimited and that works fine, if not very long winded approach.

---------------------------------------
KKhhhaaaaNNNN!!!

 

[ShellyL]

Forgot one step. After you Save As Excel, then format the Column as Text the normal Excel way. Close the file. From that point forward, any data added to the Excel file will inherit a true text format. This solution assumes you are working from the same Excel file, not creating a new one for each update.