Access won't accept linked Excel formatting

[emattson]

I have several Excel spreadsheets as linked tables in Access. Some Excel columns have all their cells specifically formatted as text, even though the data in some of those cells is a numeral. Access will not recognize the Excel formatting and insists on calling the fields a Number field (probably because it sees the numerals). Of course, I can't change the field to Text in Access, because I'm not allowed to do that for a linked table.

Anyone know how to force access to respect the Excel formatting?

Thanks,
Ed

 

[scking]

I believe you have this horse backwards. Excel will format cells differently in a single column EVEN IF YOU FORMAT THE COLUMN AS TEXT. Access reads the formatted value of the cell when it is displayed. If I use a 13 character numeric number, the National Stock Number, which should be treated as text and format it as text using the Excel formatting menus it will still display in scientific notation. I import hundreds of these files each month and am continually struggling with Excel. There is a discussion of this issue on the Microsoft Office Knowledgebase (

http://www.microsoft.com\support)

and it provides a VBA based solution to force Excel to format in a certain fashion. I have created a number of formatting macros in Excel based on this general tip that work most of the time. Try entering a text value like 12/1 and it will always change it to a date.

Public Sub Format_Text()

Dim xCell As Variant
Dim strTemp As String
Dim intPos As Integer

On Error GoTo HandleErr

For Each xCell In Selection
If Left$(xCell.Value, 1) <> &quot;'&quot; Then
If IsDate(xCell.Value) And HasTwoSlashes(xCell.Value) Then
intPos = InStr(1, CStr(xCell.Value), &quot;/&quot
If intPos > 0 Then
strTemp = &quot;'&quot; & Left$(xCell.Value, intPos - 1) & &quot;/&quot;
strTemp = strTemp & Mid$(xCell.Value, intPos + 1, InStr(intPos + 1, xCell.Value, &quot;/&quot - intPos - 1)
xCell.Value = strTemp
End If
Else
xCell.Value = &quot;'&quot; & Trim(xCell.Value)
'Debug.Print xCell.Value
End If
End If
DoEvents
Next xCell

Exit_Proc:
Exit Sub

End Sub


-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[PortPower]

I am currently stuggling with the same problem as emattson, and searching other helpsites it was recommended to order my fields so that text would appear at the top, hence Access recognising and allocating text as the data type.

In my case, the linked Excel table (which is replaced with a new file each day) has a field which has half the entries number (eg 801805), and half text (eg 801805A).

When the first row of data has a number in this field, Access allocates a data type of &quot;Number&quot;, and when viewing the data, the numbers are shown as 801805, and the text is shown as #Num!. Understandable.

However, when the first row of data has text in this field, Access allocates a data type of &quot;Text&quot;, and when viewing the data, the numbers are shown as #Num!, and the text is shown as 801805A.

If Access has designated that the Excel field contains Text, it should return every field as text, yet it returns an error for all numbers.

Confused? I am.

Peter

 

[scking]

If you look closely at the Excel spreadsheet you will see that the text and numbers are aligned on different sides of the column. That's because Excel has these formatted differently. Using the Format_Text Excel macro above, if you highlight the column and run the macro Excel will format every cell as text and Access will have absolutely not problem.

I notice that I called HasTwoSlashes within the macro. Since I didn't provide that function the macro wouldn't work. The primary part of the macro very simply places a ' before each cell which is the way Excel allows users to force the cell to be a text field. Try it by entering a number in the field and noting which side of the column it is aligned on. Now use the Excel menu to format the field as text. It shouldn't change the alignment because Excel hasn't changed it to text. Now place the ' before the numeric value and you will see it change alignment and NOT show the ' which is merely a control character.

Dim xCell As Variant
Dim strTemp As String

For Each xCell In Selection
If Left$(xCell.Value, 1) <> &quot;'&quot; Then
xCell.Value = &quot;'&quot; & Trim(xCell.Value)
'Debug.Print xCell.Value
End If
DoEvents
Next xCell

Now, if you really want to see how Excel is formatting cells use the following Excel macro. It will show you in a message box the formatted type for each cell selected. So if you put a numeric 1 in cell A1 and a numeric 1 in cell A2 preceeded by the ', the output of the message box will show the following.

Cell: A1 Double
Cell: A2 String


Public Sub GetCellFormatType()

Dim varCell As Variant
Dim strType As String

For Each varCell In Selection
'Set varCell = ActiveCell
If Left$(varCell.Value, 1) <> &quot;'&quot; Then
strType = strType & &quot;Cell: &quot; & GetRange(varCell.Row, varCell.Column) _
& &quot; &quot; & TypeName(varCell.Value) & vbCrLf
'Debug.Print varCell.Value
End If
Next varCell

MsgBox strType

End Sub

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------