Linking to Excel - #Num! in one field

  • Thread starter Thread starter Susan G
  • Start date Start date
S

Susan G

I have an Excel Spreadsheet that I got from an outside
source and have no control over what kind of data is in
it. I did a link to the spreadsheet and I am getting a
#Num! in a few of the records for the one column that has
numeric data in it except for a half dozen of the records
have an alphabetic character. On the link, it defines
this field as a double even though it has text in it. So
I went into Excel and did a format on that column and
changed it to text. I then did a link again and got the
exact same results. Please help!! Do I have to read this
excel spreadsheet in through code (if so can you point me
in a direction for documentation on this?)? If so will
the problem still persist? Are there ways to define what
each field is? I am lost here for a solution..
Susan
 
No matter what the format of the column/cell is, ACCESS decides on its own
what the format of the field will be when you link to the spreadsheet. And,
just because you have a mixture of numbers and alphanumeric characters in
cells in the first 20 or so rows, ACCESS can still decide that it should be
numeric format -- and then, alphanumeric values will show as #Num!.

Try importing the data into a table. ACCESS uses different criteria to
select the format of a field when you import, and so long as you have a
mixture of numbers and text (nonnumbers) in the first 20 or so rows, it
should correctly set the format to Text.

If that also fails, then you can put ' characters in front of the numbers in
that column and then import the spreadsheet. That will tell ACCESS to use
text format.

And if that should fail, the "big stick" is to use Automation to open the
EXCEL file, read each row's cells, and write them directly into a recordset
that is bound to a table.
 
Back
Top