Linking Access and Excel Tables

[Serene]

When I created a new table in Access by linking it to an
Excel table it maps one of the fields as a number field
when in fact it should be text. Access won't let me
modify the field type from number to text because of the
link. If I format the field in Excel as text before I
create the linked table in Access it makes no difference.

Any ideas / fixes would be greatly appreciated.

Regards,

Serene

 

[John Nurick]

Hi Serene,

This is a common problem. Here are two ways round it.

1) Make sure that all the values in the first dozen or so cells in this
column in the Excel table are text. If there's a single numeric value
here, Access will perversely ignore the text values and link the column
as a number field. (It's different when you import: then, a single text
value is enough to have it imported as a text field.)

2) Prefix each number with an apostrophe ' . This forces Excel and
Access to treat the number as text, but the apostrophe isn't displayed
in the worksheet or taken through into Access.

These little Excel VBA routines will add and remove apostrophes:

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

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

 

[Serene]

John,

Thanks for you assistance - much appreciated.

Regards,

Serene

-----Original Message-----
Hi Serene,

This is a common problem. Here are two ways round it.

1) Make sure that all the values in the first dozen or so cells in this
column in the Excel table are text. If there's a single numeric value
here, Access will perversely ignore the text values and link the column
as a number field. (It's different when you import: then, a single text
value is enough to have it imported as a text field.)

2) Prefix each number with an apostrophe ' . This forces Excel and
Access to treat the number as text, but the apostrophe isn't displayed
in the worksheet or taken through into Access.

These little Excel VBA routines will add and remove apostrophes:

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

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

When I created a new table in Access by linking it to an
Excel table it maps one of the fields as a number field
when in fact it should be text. Access won't let me
modify the field type from number to text because of the
link. If I format the field in Excel as text before I
create the linked table in Access it makes no difference.

Any ideas / fixes would be greatly appreciated.

Regards,

Serene

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.