Importing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michelle - 14 Sep 2004 06:04 GMT

I am trying to import a spreasheet from Excel into an
existing table in Access. Most of the data is imported
correctly, except for 1 field. The field that does not
import has some values that are text and some that are
numbers. Access only imports the values that are
numbers. The table I am trying to import data into is
designated as a memo type.

How can I import eveything from the spreadsheet as text?

(I have already tried formatting the column in Excel as
text, but Access still tries to import all the data as a
number)

Thanks!

Reply to this Message

John Nurick - 14 Sep 2004 07:11 GMT

Hi Michelle,

Usually the simplest way to fix this is to modify the Excel table to
make sure that at least one value in the first few rows of the column in
question is something that cannot be interpreted as a number.

One way is to add a dummy row at the top of the Excel table, and delete
it after importing.

Another is to prefix the numbers in the text column with an apostrophe
'; this forces Excel and Access to treat them as text strings not
numeric values.

You can use this VBA procedure in Excel to add apostrophes to all
numbers in the selected range of a worksheet:

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

>I am trying to import a spreasheet from Excel into an
>existing table in Access. Most of the data is imported
[quoted text clipped - 11 lines]
>
>Thanks!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message