Linked Excel file gives #Number! error in table

[Ray C]

My code links to an Excel file and copies the data from the linked file to an
Access table using queries.

The problem is that when I double-click the linked Excel file within Access,
one of the rows, on one column there is #Number ! showing up.

I opened the Excel file separately, and it has data in that field. It looks
like Access cannot read that one particular cell. Access interprets it as a
text field which is what I want.

I tried changing the cell format inside Excel to Text but I still get the
same problem.

What could be causing this one field to be misinterpreted?

Thanks

 

[OssieMac]

Hi Ray,

"It looks like Access cannot read that one particular cell". bit hard
without actually knowing what data is in the column. What type of data is in
the remaining cells in that particular column? ie. is all the data in the
column the same type. You said that you want it in text. This suggests that
the column has numbers also. Perhaps it is worth creating a virtual field
(Expression) in the Query for that particular column and force it into text
with something like

format(fieldname,"@")

or maybe add a column in Excel and use =TEXT(address,"@") and copy, paste
special, values and delete the old column and then format the entire column
to text.

Also, does the table get created during the import or are you importing to
an existing table?

 

[Ray C]

The other values in the column have values like "343787-1" and "78437-01".
The reason why I made it a text field is because there are hyphens in the
data. I'm ok with this. BUT, the field that is giving me the problem has
"748548". In other words, it has no hyphens.

My import logis is like this...

1) Link to the Excel file using DoCmd.TransferSpreadsheet.
2) Use an SQL query "SELECT blah,blah INTO tblNewTable FROM LinkedExcel.

The Access table is recreated each time.

Please note however, that the problem starts at point 1).
When I open the link, that's where I see the #Number! in that one field.

Here's another important point. When I opened Excel and I forced the field
to be a text field, I got the small icon next to the field (yellow diamond
with an exclamation mark inside it) asking if I want to convert it to
numeric, which I ignored.

When I imported at that point, everything worked.

Here's another question. How can I make sure that the user has formatted the
entire column as Text, before importing? Access does not pop up any error
messages when this happens, it imports the table but the cell in question
shows up as a null (blank) value. The user might think that there's something
wrong with the import code, when in fact, it's his excel file.

Thanks!

 

[Paul Schrum]

Ray,

I was struggling with the very same problem you describe and searched
the groups for a solution just today. Restating, the problem is that
numbers in Excel appear as errors when viewed from Access via linking
to the Excel spreadsheet (#Num is the error).

I found a different solution which you find of value:

In the Excel file I created a new worksheet in which every cell simply
copies its sibling cell from the original workbook:

='OtherWorkbook'!A1

I then edit those same cells again to wrap the "equation" in the
CONCATENATE formula so I can tack on a blank space at the end:

=CONCATENATE(INDEX('OtherWorkbook'!A1," ")

I hope you find this useful.

- Paul Schrum
AECOM, Raleigh
(Formerly Earth Tech)

 

[OssieMac]

Hi again Ray,

I did some testing and the interesting thing is that while the error occurs
when Linked to the xl worksheet, it does not occur if the xl worksheet is
imported into a table. Perhaps a workaround is to import the entire worksheet
into a table first and then run your Query on the table. As it is apparently
a Make Table query, you can delete the unwanted table when finished.

 

[Ken Snell \(MVP\)]

See Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or
when Linking to an EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#DataTypeErr