Any solutions out there? Keep in mind I have thousands
of cells to correct.
Posted by John Nurick on 11/13/2003:
When you look at the linked table, are the values in the "mixed" column
displayed properly? My guess is that they are not: when *linking* to a
column with a mix of numeric and non-numeric values, Access links it as
a *number* column if there is even one numeric value in the first dozen
or so rows.
If you *import* from Excel instead, the rule is different, and a single
*text* value in the first rows will force the column to be imported as
text, which is what you need.
Another approach is to modify the values in the worksheet so Excel
itself treats them as text. Prefixing each cell's contents with an
apostrophe will do this; the little procedures below can handle this.
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
< end John's post >
--
Ken Snell
<MS ACCESS MVP>
"MaryB." <anon...@discussions.microsoft.com> wrote in message
news:01d801c3aaf6$0281bec0$a501...@phx.gbl...
The trick with the apostrophes hasn't failed me yet, but linking to
Excel does seem to be a bit flaky so I'm not very surprised to hear that
it doesn't seem to have worked for you.
But let's make certain we're both thinking along the same lines.
Re-reading your first message I see you said
>>> All are numbers
>>> except for a few in the 1st row (in Excel)that are
>>> words.
1) Does this mean that the first row is field names and the remaining
rows contain only numbers? If so, the apostrophe trick shouldn't be
necessary, but every cell in the first row must contain a valid Access
field name, and cells in other rows must contain numbers only (or be
empty). Make certain they don't contain mistyped numbers such as "O" or
"l", or text such as "-" or "n/a". To find these and other non-numbers,
you can use a formula like this
=IF(ISNUMBER(A1),"","--> " & A1)
in all the cells in a blank column.
2) If the first row is data with a mix of text and numbers, then use the
apostrophe trick on all the numbers in every column that has text in the
top cell.
3) A separate issue: do the cells contain actual numbers and text, or
formulas that produce numbers and text? Access doesn't always import
formula results correctly.
Importing from Excel is definitely less troublesome than linking.
Sometimes won't data won't even import correctly from a worksheet; in
that case save it as a text file and then import the text file to
Access.
Hope this helps; if not, post back with more details of just what's in
your cells and what's happening. Also: what version (and service pack)
of Office are you using?
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
Does that fix things?
--
Ken Snell
<MS ACCESS MVP>
"MaryB." <anon...@discussions.microsoft.com> wrote in message
news:07d201c3ad15$046f7e40$a401...@phx.gbl...
On Mon, 17 Nov 2003 19:39:59 -0500, "Ken Snell"
<kthiss...@notcomcast.realnet> wrote:
>If you put the apostrophe into the spreadsheet while it was still linked to
>ACCESS, then no, it won't work because ACCESS remembers the original format
>of the column (field). You must delete the link to the EXCEL spreadsheet,
>then edit the spreadsheet's data, and then reestablish the link to the
>spreadsheet via ACCESS.
>
>Does that fix things?
--
>.
>