Here's a canned summary I put together some while back re: that #Num
error. Excel is
"loosely typed" and can have multiple data types in the same column ---
each cell has it's own data-type irrespective of how the formatting is
set.
As to possible differences between workstations ... how was the Excel
file copied to your workstation? Is it in fact an *identical* copy, or
is it possible that some translation could have occurred when making the
copy?
Here's the summary:
Here is some information I have gleaned over time regarding the #NUM!
error when importing or linking to Excel spreadsheets.
Excel stores cell values as a variant, and regardless of how you format
the entire column you can end up with different variant data types on a
cell by cell basis.
This becomes a nasty problem because the 'transfer spreadsheet' function
that Access uses to either import or link from an Excel worksheet does
not do any data type conversion. If you 'trick' Access into expecting
text data and one of the Excel cells has a number(or date) data type you
will get the #NUM error; conversely if you have everything formatted as
numeric(or date) and Access comes across a text datatype cell you will
get the #NUM error. (Empty cells properly come across as Null in either
case.)
I have also found that a cell containing a single space (in an otherwise
numeric or date column) will link as #Num!.
Threads containing additional discussion:
http://groups.google.com/group/micr...read/809542461417afdf/7440746c9fa31c61?q=#num
http://groups.google.com/group/micr...read/b4b7748816387977/f8eaa8f9c95bc766?q=#num
Barry Gilbert discussing multiple data types in an Excel comumn:
http://groups.google.com/group/micr...roup:microsoft.public.access#a8b5bcd4f77450d9
This post by Mark shows a macro to force Excel cells to text data type
and another to force cells to a numeric type:
http://tinyurl.com/3vt5rm
Van T. Dinh finds the easy way is to modify the Excel file as follows:
* Insert a "calculated" Column next to the MixedColumn
* Link the Excel file to Access, ignore the original MixedColumn and use
the "calculated" Column. All values in this Column will be Text so the
values won't have #NUM entries.
His discussion is at
http://tinyurl.com/4vf6uv .
One possible work-around is to use the procedure in this KB article to
force every cell to text type in the Excel sheet:
(815277) - Explains the "Numeric Field Overflow" error message that
occurs when you query a table that is linked to an Excel spreadsheet.
This article provides a workaround to resolve this problem. Requires
basic macro, coding, and interoperability skills.
http://support.microsoft.com/kb/815277/en-us
Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or
when Linking to an EXCEL File (additional discussion of Jet, possible
registry hack)
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#DataTypeErr
From MS Access MVP Roger Carlson:
Because I've had so much trouble with linking Excel files into Access
(previous to Access 2007, you had no control over the datatype), I've
taken to ALWAYS linking them as CSVs. That way I can define the
datatype in the Import Specification. (
www.rogersaccesslibrary.com)
