changing properties in linked excel files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Excel file I 've linked to a MS Access project. One of the columns
consist of letters and number combination (Ex DL1234) or just numbers (Ex
1234). When linked, MS Access automatically defaults this column information
to Number property and I am unable to modify this property. This causes
errors in the table as any number/letter combination is kicked out and is
replaced with #Num! error coding. Is there anyway around this? I tried
using Text or General for the cell property in Excel but this doesn't change
anything.
 
Hi Lois,

There's no way of telling Access to link or import an Excel column as a
specific data type. It's possible, up to a point, to change the rules it
applies as it 'guesses' the data types to use, but IMHO the best
solution is usually to change the data in the Excel sheet to ensure that
it is treated as text.

The way to do this is to put an apostrophe in front of each numeric
value in a column with mixed data types. It does no harm if you do it to
all values, e.g.

'DL1234
'444532

The apostrophes don't show up in the Excel worksheet or in Access, but
they force both programmes to treat the values as text even if they look
like numbers or dates. Here's an Excel macro:

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Someone may suggest inserting a dummy first row with a text value in
this column. This works when you are importing the data, but the rules
are applied differently when linking.

For the rules, see e.g.
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
http://support.microsoft.com/?id=257819

Finally: Access 2007 will allow you to specify the field types.
 
I found this solution in another post and it works great with very little
effort. When you have a combination column of text/number but you want it
all linked into Access as text just create a calculated column as follows:

=Text(A2, "General")

Type the above as is only use your correct cell reference. When you LINK
this column into Access it will read both numbers and letters as just text.

LMJ
 
Back
Top