#NUM error when linking tables

[newton1234]

Hi,

I am trying to Link a table from Excel to Access which the rows contain both Numeric and Text values.

However when I link it the fields come through as #NUM.

I have tried formatting the cells in Excel to Text then running the below Macro Code, however some of the cells are formulas which the Macro knocks out.

This is the Macro I am using :

Sub Addspace()
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

Does anyone know a way of linking the table and bringing through the values ?

Thanks

 

[marleymanner1]

Try this stuff:

1) if your Excel sheet has formulas instead of values that you want bring over simply as values, copy the entire sheet into a NEW sheet first, and paste special as VALUES. Then link that sheet to the Access table instead of the one with the formulas. That will solve this problem right away (VB is available for this, but it is complicated code).

2) To fix the #NUM error, simply change the data types of your Access fields from NUMBER to TEXT. That's what is causing it. You mentioned that the Excel fields are alphnumeric. These are automatically read as text fields in Access. If you already have a field formatted as NUMBER, you will probably get the error, because it's not an actual number.

Don't hold my word to all of this, but I'm pretty sure it's close, if not right on.

 

[newton1234]

Thanks for your reply.

The paste special would be the best way to do it, however my boss has set me this task which is to link in 10 spreadsheets to Access so he can report on them, therefore he does not want any manual work at all. These linked tables need to be changed every month and I believe asking him to paste special every month would not go down well :-( (The linking needs to be done by Macro or Module)

I tried changing the format when it is in access, however that never worked.

I have attached a mock spreadsheet of similar standard to what I have to link, if this is any help to give me a solution ?

Thanks again.

 

[Fozz]

Hi Newton1234,

I have the same symptoms. For the data that Access shows as #NUM, the corresponding cells in Excel "looked empty" but in fact had 1 blank in them. I'm in the processing of finding those cells and deleting the blank.

Good Luck,
Bill

 

[DCrake]

If you calculated cells are in horizontal in a column then don't import this column let access do the calculation.

If you formulas appear underneath a range then what you need to do is to create a new worksheet that references the actual data without the sub totals and import this worksheet.

Otherwise you could enter the range you want to import and do it in sections.