Importing from Excel problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 13 Nov 2003 12:36 GMT

I am trying to import data from an Excel file to Access
table by using an update query (Excel file is linked).
Everything is going right except one column.
In Excel it contains numbers from 1 To 9999 and sometimes
a letter + 2 digits (like D32).

Destination field properties in Access table are: Text;
maximum 15 characters; no any restrictions; no masks.
The table is not in relation with other tables.

So, after the import process is finished I do not get any
error messages. All the records are imported. But in that
very column the number-values are there and letter-values
are not (just empty cell).
What is wrong? Any ideas?

Reply to this Message

Mirco Wilhelm - 13 Nov 2003 13:07 GMT

> I am trying to import data from an Excel file to Access
> table by using an update query (Excel file is linked).
[quoted text clipped - 11 lines]
> are not (just empty cell).
> What is wrong? Any ideas?

I hope i got this right...

You have an Excel table like

A B C... AA... AAA... AAAA...
1
2
3
4
5
6
...

D32 would be a cell in column D, row 32. After import the destination cell
in access is empty. If it was a type mismatch Access would have shown an
error message, so are you sure this cell has any value?

couldn't make more of the discription, maybe you could post more specific
datails on your problem.

___
mirco

Reply to this Message

John Nurick - 13 Nov 2003 18:52 GMT

Hi Mike,

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

>I am trying to import data from an Excel file to Access
>table by using an update query (Excel file is linked).
[quoted text clipped - 11 lines]
>are not (just empty cell).
>What is wrong? Any ideas?

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Thanks guys!! from a grateful John Pritchard from the UK. - 14 Nov 2003 12:52 GMT

I had exactly the same problem and this solution looks to
be just what I need - Cheers John and good luck Mike.

>-----Original Message-----
>Hi Mike,
[quoted text clipped - 50 lines]
>Please respond in the newgroup and not by email.
>.

Reply to this Message

TIFFANY - 05 Dec 2003 20:20 GMT

I'm having a SIMILAR problem, however my tables have to be
linked not imported. I need the data to be real-time due
to changes made to them on a daily basis from users
accross our network.
Again, I have numeric and date formatted cells that
sometimes have text entered in them...i need both types of
data to show up.... how do i accomplish this ???

helpppppp

>-----Original Message-----
>I had exactly the same problem and this solution looks to
[quoted text clipped - 69 lines]
>>
>.

Reply to this Message

John Nurick - 06 Dec 2003 21:43 GMT

Hi Tiffany,

This is not a good situation to be in. If you have columns in Excel that
contain mixed text and numeric (or date) values, you have to force
Access to treat them as text columns, and the only reliable ways to do
that are the ones I described in my previous post.

Both these, however, have implications for the functionality of the
Excel workbook, and - particularly if you have multiple users
promiscuously updating the worksheet - it is also *very* difficult to
set up data validation in Excel to ensure that users can't modify the
worksheet in a way that screws up the link to Access.

So basically a far better approach is to store the data in Access -
which has proper multi-user capability and data validation built in -
and modify the Excel application to retrieve and update the Access data
as required.

>I'm having a SIMILAR problem, however my tables have to be
>linked not imported. I need the data to be real-time due
[quoted text clipped - 80 lines]
>>>
>>.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message