Importing From Excel Using Import Spreadsheet Macro



I have tried to import data supplied to me in a spreadsheet which contains
reference to an employee number ... this is in the main a seven digit numeric
value, however there are a number which contain 6 numbers and a letter at the
end ... problem appears to occur when Access reads the data ... assumes all
are numeric and omits the alphanumeric values !

Is there a way when importing data using a macro that you can specify that
employee_no is a text field and should be imported as such ??

Many Thanks in advance

John Nurick

Hi Alan,

There are several ways.

1) One of the best is to prefix each value in Excel with an apostrophe,
e.g. '987654 instead of 987654. This forces Excel to treat the numbers
as text, but the apostrophes don't show up on the worksheet or get
imported into Access. Here are a couple of VBA procedures that will add
apostrophes to every value in a column, or remove them from the selected

Sub AddApostrophesAllToColumn( _
ByVal TheColumn As Long _
Dim C As Excel.Range
With ActiveWorkbook.ActiveSheet
For Each C In Intersect(.Columns(TheColumn), _
C.Formula = "'" & C.Formula
End With
End Sub

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
End Sub

2) Sometimes it's enough to ensure that the first value in the column in
question cannot be interpreted as a number. E.g. if the first value is
123456A. Or try creating the table in Access first, with the field types
you need, before importing.

3) For the deep and dirty, see

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question