Importing

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

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!
 
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
 
Back
Top