#Num! Error when linking Excel to Access

[mrhartwig]

Hi there. I have been trying to link an excel spreadsheet to an
access table all day and have been running into a problem that seems
pretty common. Unfortunately, all the tips I've seen posted have not
resolved my error. I have a spreadsheet with approximately 15
columns. All the columns but 1 will link to access without any
problems. I have 1 column that contains data similar to this: 315.991
For some reason most of the data is coming over but out of 791 records
approximately 100 give me the #NUM! error.

I've tried changing the format in excel to text.

I've tried inserting "dummy" fields above my data to trick access into
formatting all my data as text.

I've even tried the following Macro:
Public Sub FormatAsText()
Dim cell As Object
For Each cell In Selection
cell.Value = " " & cell.Value
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Next
End Sub
This one sort of worked.....it dropped the 1 digit off all my data.
So instead of 315.991 it was 15.991.

Any other ideas out there? Any information would be greatly
appreciated! Thanks so much!!!

--Michelle

 

[Tom Wickerath]

Hi Michelle,

Try exporting your worksheet to a comma separated value (*.csv) text file.
For one of the cells that includes data that results in a #Num error when
linked, what do you see in the text file for the corresponding cell?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[mrhartwig]

Tom,

Thanks for the response. I tried what you suggested and exported my
spreadsheet to a csv file and I didn't see a difference. All the
cells that I rec'd the #num! error on looked the same as they did in
my excel spreadsheet. Any other ideas?

 

[Clif McIrvin]

Hi Michelle-

I *hate* that #NUM error!

I like Tom's suggestion --- I'd never thought of that.

What I have found is that 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.

The problem becomes nasty 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 follow Bonnie's suggestion and
'trick' Access into expecting text data and one of the Excel cells has a
number data type you will get the #NUM error; conversely if you have
everything formatted as numeric and Access comes across a text datatype
cell you will get the #NUM error.

As to the knowledgebase code that butchered your data --- that is
specifically designed for creating TEXT cells, and IIRC step one is to
format your column as TEXT. If your cell data type is numeric Excel
will lose that leading space and the RIGHT() function will strip your
leading digit.

 

[Tom Wickerath]

Hi Michelle,

Would it be possible for you to send me a copy of your spreadsheet? I'm
willing to try to help you, if I can have something that allows me to
duplicate the #Num errors you are experiencing. If you are interested, send
me a private e-mail message with a valid reply-to address. My e-mail address
is available at the bottom of the contributor's page indicated below. Please
do not post your e-mail address (or mine) to a newsgroup reply. Doing so will
only attract the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________