Importing From Excel Using Import Spreadsheet Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
cells.

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

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
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
http://www.dicks-blog.com/excel/2004/06/external_data_m.html
and http://support.microsoft.com/?id=257819
 
Back
Top