#NUM! error in Linked Excel Spreadsheet

[GohDiamond]

When I link an Excel spreadsheet in MS Access 2003 and view the Linked resource, for certain fields I see the #NUM! error in some of the records, valid alphanumeric data entries in those same fields for other records.

Looking in the original source file with the native Excel application, those fields in those records that appeared as #NUM! have just numbers in that field.

If I append the linked resource information to a table structured to receive each field with header names corresponding exactly to the resource, the entire field is left blank/empty for all records.

However if I use the "docmd.transferspreadsheet" command to simply append the same data from that same resource to the Access table, it captures every record just like it's supposed to in WYSIWYG fashion.

Any idea why the Linked excel resource doesn't display the data when the link is viewed or append the data to the table properly?

Cheers!
Goh

 

[zoiboi]

In the cells that give the error are there equations or external links?

 

[GohDiamond]

There are no external links or formulas/equations, just hard coded data in the field. Like:

FieldName
abc123
123456
<blank> <- meaning this field in this record has nothing entered in it

just with those variations in field content.

Any ideas as to why this issue occurs?

thanks,
Goh

 

[Fear Naught]

I think this is because the columns in the spreadsheet have a mix of data types (from your example). I believe that Access tries to determine the datatype of the columns in the linked spreadsheet and fails hence the error.

 

[zoiboi]

That would be unusual..Access should default to Text format for the field when creating the table if the data is mixed.

If there is a data type mismatch Access usually says it can't import the data and either aborts or deletes the "bad" data from the import. It shouldn't give a #num error.

I really think the issue is on the Excel side. The data isn't in a pivot table is it?

 

[GohDiamond]

No, not in a pivot table. I believe Kevin's response relects the actual results that we see, but I did think just like "Z" that Access would default to text in the case of mixed (alphanumeric) source data in the same field.

The best solution I could find was to use DoCmd.TransferSpreadsheet and that gave me the WYSIWYG transfer I was looking for; just had to build a table for the data to be transferred to with the field types pre-defined.

Cheers!
Goh

 

[Brianwarnock]

Access bases its field type on the first cell, but the real problem is that Excel is not storing the numbers as text, there are a number of ways to achieve that but changing the cell format after the number is entered is not one of them, ok the number becomes left justified in the cell but is still a number !!

You must either enter the numbers in cells predefined as text or you can convert by appending ' in front, a simple macro will do this, ok this isn't strictly converting but the the effect is the same

eg

Sub chge()
For Each c In ActiveSheet.UsedRange
If IsNumeric(c) Then
c.Value = "'" & c.Value
End If
Next c
End Sub

but having numbers flagged as text makes the spreadsheet look messy and I don't know and haven't tried to do a mass "ignore this error"

i suspect your workaround is your best solution.

Brian