Access Reformats Linked Excel Table from Text to Numbers

[Gabe]

I had formated an Excel table for all cells to be seen as
text. When I linked it to a form I can see in Design
view that Access has changed some of the cells to be seen
as numbers, I also get the #Num! error prompt in my form
and it won't let me enter text. I have gone back to the
Excel sheet to specify text format again but Access
continues to see certain cells as numbers only. Is this
a bug? Does anyone have any experience? Thanks, Gabe

 

[John Nurick]

Hi Gabe,

Access mostly ignores the Excel cell formats and just looks at the
actual cell contents. To make things worse, when you're linking, if
there's a single numeric value in the first dozen or so cells in a
column Access assumes that the entire column is numeric and gives you
the #NUM! error for all the non-numeric values.

One way round this is to prefix the numeric values in the cells with an
apostrophe (e.g. '123 instead of 123). This forces Excel to treat the
value a text regardless of the cell formatting, and Access picks this
up. The apostrophe isn't displayed in the Excel grid and isn't taken
across to Access.

Here are a couple of little macros that can add and remove these
apostrophes from the selected cells:

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

 

[Gabe]

Thanks John, Can I just add the apostrophe's to my linked
Excel table, or do I need to unlink my table and add the
apostrophe's and then relink? Will Access then see things
the way I want?

-----Original Message-----
Hi Gabe,

Access mostly ignores the Excel cell formats and just looks at the
actual cell contents. To make things worse, when you're linking, if
there's a single numeric value in the first dozen or so cells in a
column Access assumes that the entire column is numeric and gives you
the #NUM! error for all the non-numeric values.

One way round this is to prefix the numeric values in the cells with an
apostrophe (e.g. '123 instead of 123). This forces Excel to treat the
value a text regardless of the cell formatting, and Access picks this
up. The apostrophe isn't displayed in the Excel grid and isn't taken
across to Access.

Here are a couple of little macros that can add and remove these
apostrophes from the selected cells:

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

I had formated an Excel table for all cells to be seen as
text. When I linked it to a form I can see in Design
view that Access has changed some of the cells to be seen
as numbers, I also get the #Num! error prompt in my form
and it won't let me enter text. I have gone back to the
Excel sheet to specify text format again but Access
continues to see certain cells as numbers only. Is this
a bug? Does anyone have any experience? Thanks, Gabe

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

I don't know for sure, Gabe, but I think you'll have to relink in order
to let Access change its mind about the field types.

Thanks John, Can I just add the apostrophe's to my linked
Excel table, or do I need to unlink my table and add the
apostrophe's and then relink? Will Access then see things
the way I want?

-----Original Message-----
Hi Gabe,

Access mostly ignores the Excel cell formats and just looks at the
actual cell contents. To make things worse, when you're linking, if
there's a single numeric value in the first dozen or so cells in a
column Access assumes that the entire column is numeric and gives you
the #NUM! error for all the non-numeric values.

One way round this is to prefix the numeric values in the cells with an
apostrophe (e.g. '123 instead of 123). This forces Excel to treat the
value a text regardless of the cell formatting, and Access picks this
up. The apostrophe isn't displayed in the Excel grid and isn't taken
across to Access.

Here are a couple of little macros that can add and remove these
apostrophes from the selected cells:

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

I had formated an Excel table for all cells to be seen as
text. When I linked it to a form I can see in Design
view that Access has changed some of the cells to be seen
as numbers, I also get the #Num! error prompt in my form
and it won't let me enter text. I have gone back to the
Excel sheet to specify text format again but Access
continues to see certain cells as numbers only. Is this
a bug? Does anyone have any experience? Thanks, Gabe

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.