HELP !!! Problems when linking to Excel File

[Joe Blow]

Hello All:

Hope someone can help. When I link to an Excel file, one field contains
mixed alpha and numeric data.. ie:

123
456
ABC
DEF
789

What I see in Access in the linked table is:

#NUM
#NUM
ABC
DEF
#NUM

I've changed the Format in Excel to "Text" instead of "General" and it is
still doing it...


Any ideas?

Oh, both Access 2000 and 2003 are doing this... And the Excel file is Excel
2000 (I think).

Thanks,

Joe

 

[Joe Blow]

Also,

If I import the data, it comes in ok.
But Import is not a feasible option for me, because the data in the Excel
File changes daily.
And I don't want to have to do an Update Query on a daily basis.

I just need read-only access to the Excel file. I will not be making any
changes myself.

Thanks Again,

Joe

 

[Bob]

Also,

If I import the data, it comes in ok.
But Import is not a feasible option for me, because the data in the Excel
File changes daily.
And I don't want to have to do an Update Query on a daily basis.

I just need read-only access to the Excel file. I will not be making any
changes myself.

Thanks Again,

Joe

Joe: Look at this article in the Knowledge Base: 815277

Bob

 

[Dodo]

Hello All:

Hope someone can help. When I link to an Excel file, one field
contains mixed alpha and numeric data.. ie:

123
456
ABC
DEF
789

What I see in Access in the linked table is:

#NUM
#NUM
ABC
DEF
#NUM

I've changed the Format in Excel to "Text" instead of "General" and it
is still doing it...


Any ideas?

How did you change the cell format? By selecting the column and change
Properties?

That method works for cells in that column that were not formatted
individually. Individual cell formats have priority over formats per
column.
You have to select the cells themselves to change the format to text.

And then you have to modify the link definition by removing the spreadsheet
from the tables section and re-establishing the link to the new version of
the spreadsheet.

 

[John Vinson]

Hello All:

Hope someone can help. When I link to an Excel file, one field contains
mixed alpha and numeric data.. ie:

123
456
ABC
DEF
789

What I see in Access in the linked table is:

#NUM
#NUM
ABC
DEF
#NUM

I've changed the Format in Excel to "Text" instead of "General" and it is
still doing it...

Since Excel does not have datatypes for cells - just formats, which is
a different matter - Access must guess at the appropriate datatype
based on the contents of the first few rows of the spreadsheet. If you
have a few rows where the cells all contain nothing but numbers,
Access guesses (incorrectly) that the field type is numeric.

For a linked spreadsheet, about the only solution I know is to put a
"dummy" first row in the spreadsheet with an unambigouously text value
in each column which contains mixed text and numbers.

John W. Vinson[MVP]