Access Reformats Linked Excel Table from Text to Numbers

  • Thread starter Thread starter Gabe
  • Start date Start date
G

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
 
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
 
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
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
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
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top