Importing data from excel to access

[Guest]

I have a very large excel spreadsheet with two colums that are giving me
problems when I try to import it into access. One column is with zip codes in
two formats (80898 and 808908-5768) for example. The other has numeric data
separated by commas (3836, 2837, 2928). I've tried converting these cells to
text format but I still get a type conversion error when I import it. Any
ideas?

 

[John Nurick]

Hi Patrick,

In my experience the best thing to do is to prefix an apostrophe to each
of the values in these fields. That forces Excel and the import routine
to treat them as text rather than numbers. THe apostrophes don't show up
in the worksheet or get imported into the database. (If the values are
the result of formulas you'll also need to convert them to literal
values by doing Copy and then Paste Special|Values.)

These Excel VBA procedure will respectively add apostrophes to the cells
in the selection or the specified column (excluding empty cells and
cells containing formulas):

Sub AddApostrophesAllToSelection()
Dim C As Excel.Range
With ActiveWorkbook.ActiveSheet
For Each C In Selection.SpecialCells(xlCellTypeConstants).Cells
C.Formula = "'" & C.Formula
Next
End With
End Sub

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