Linking Problem from Excel to Access

  • Thread starter Thread starter MaryB.
  • Start date Start date
M

MaryB.

I have a very large spreadsheet in Excel that I want to
Link to Access. Various columns have numbers, some have
text & numbers. PROBLEM: Some of the cells' contents
show up in Access as: #Num! I can't find any any logic
as to which cells it does this to. All are numbers
except for a few in the 1st row (in Excel)that are
words. I have come to the conclusion thru much searching
on the HELP screens that the problem is with Excel & how
the cells are formatted. I have tried everything I can
think of to correct this problem & nothing works on all
of the problem cells. I have tried a variety of
things...reformatting cells, copying & pasting the cells
to a new spreadsheet, etc. I even got so desperate I
tried working on individual cells, such as:
Reformatting the cells to TEXT & retyping the numbers.
Reformatting the cells to TEXT & editing the cells to
put an apostrophe in front of the number.

Any solutions out there? Keep in mind I have thousands
of cells to correct.
 
ACCESS will identify the format of the field based on what's in it. John
Nurick has posted some info on this just recently.... let me see if I can
find it... ah, here it is (note the last sentence in the first paragraph):

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 >
 
I read the msg by John Nurick. I tried putting the
apostrophe in - it didn't work. Any other ideas?
 
Hi Mary,

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
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?
 
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?
 
Good catch, Ken!

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?
Click to expand...
 
-----Original Message-----
Click to expand...
Thanks guys! I actually figured out what the problem was
& how to solve it yesterday (Monday 11/17). A tip here &
there, & lots of playing around (I couldn't begin to
count the hours I spent on this problem!)
I have Access 2000 (if that matters).
I had most columns (in Excel) with some fields text &
some fields #'s. What I finally thought I saw as a
pattern, was:
1.) After Linking, if the Data Type came up as Number,
then the cells with text in them showed up as #Num!
2.) After Linking, if the Data Type came up as Text, then
the fields that actually contained numbers were showing
up as #Num!
(Why would text in a Number field say: #Num! AND a number
in a Text field also say: #Num!...??)
3.) A BIG problem in figuring this out was the fact that
in Excel, reformatting a column to Text after a number
has been typed in really doesn't change it to Text. I
didn't know how the numbers on my spreadsheet were
entered in the first place.
SOLUTION:
1.) I sorted my spreadsheet (in Excel) in such a way as
to make sure the 1st 30 rows definately contained text.
2.) After Linking, all of the Data Types were Text, &
some of the cells said #Num! (These all looked like #'s)
3.) I went back to Excel & put the apostrophe in front of
the problem numbers.
4.) IT WORKED!!!
Why some of the other goofy things that I saw along the
way happened, still makes no sense to me, but it's
working now, who cares!
Thanks again!
Mary



Hi Mary,

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
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?


I read the msg by John Nurick. I tried putting the
apostrophe in - it didn't work. Any other ideas? the
first paragraph): when
*linking* to a in
the first dozen to
be imported as can
handle this. in
message cells
to
Click to expand...

--
John Nurick [Microsoft Access MVP]

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