Data showing in Access as #NUM! for linked Excel Table

[wabahn]

I have a link from Access to an excel spreadsheet. Several columns have text data with a periodic cell that the data is a number. When I look at the table from access, I see #NUM! in the cells that have numbers instead of text. I have tried reformatting the data in Excel to be generically text, however, Access doesn't recognize the data.

I want to use the table as part of an append query, however, it gives me null values in those fields. Any ideas on how to resolve.

 

[AnotherHiggins]

Text <> Number

-> reformatting the data in Excel to be generically text
General <> Number
General <> Text

....

What's happening is that when you're importing the table, Access is guessing what kind of data is in each column based on the first few rows of data. Since you have two kinds of data in this column, you must format the column specifically as TEXT, do not format as General.

Depending on how the data in the source sheet is populated, you might have to force each of the number entries to be recognized as text. A good way to tell how excel is treating the data is to turn off any special horizontal alignment for the column (not right or left justified or centered).
[ul][li]Text entries will default to being left aligned[/li]
[li]Number entries will default to being right aligned[/li][/ul]
If the numeric data defaults to left alignment, then you know that Excel is really treating it as text, not a number. You should be ready to import that table into Access. On the appropriate screen in the import wizard, make sure that Access is importing this column as text.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[wabahn]

AnotherHiggins - thanks for the reply.

Although the reformatting to text using format cells didn't work it did get me searching Excel help for other ways to convert numbers to text. I was able to resolve the #NUM! issue when viewing a linked table in Access by using the TEXT function in Excel.

My original data were located in columns D & E and I had empty columns in I & J. Here is the code that I used to copy the data in columns D & E to TEXT format in columns I & J

Range("I2").Select
Do Until Selection.Offset(0, -5).Value = ""
ActiveCell.FormulaR1C1 = "=TEXT(RC[-5],""0"")"
Selection.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-5],""0"")"
Selection.Offset(1, -1).Select
Loop

-------------
:-D

 

[AnotherHiggins]

FYI: You can do it in place. I have the following code assigned to a button on a custom toolbar in Excel 2003.

Sub ConvertToText()
'   Written By:     AnotherHiggins
    Dim rngCell                              As Range
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    For Each rngCell In Selection
        If IsNumeric(rngCell) And Len(rngCell) > 0 Then
            With rngCell
                .Value = "'" & .Value
            End With
        End If
    Next rngCell
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.