Data type conversion from Excel

  • Thread starter Thread starter Jon
  • Start date Start date
J

Jon

I am trying to import an Excel spreadsheet. The fields in the
spreadsheet include a 6-digit code, some with leading zeros. These
cells are formatted as text. In the Access import wizard, the data type
in the field options is grayed out and it is importing the field as a
double. Is there any way to control the format on the import?


Jon Cosby
 
you could save the Excel file as a .txt file, then import the .txt file.
using the Access import wizard, you have almost complete control of how data
imports from a text file.

hth
 
I am trying to import an Excel spreadsheet. The fields in the
spreadsheet include a 6-digit code, some with leading zeros. These
cells are formatted as text. In the Access import wizard, the data type
in the field options is grayed out and it is importing the field as a
double. Is there any way to control the format on the import?
Click to expand...

If you prefix the values in the Excel cells with an apostrophe ', it
forces them to be treated as text. These Excel VBA functions may help:

Sub AddApostrophesNumericToSelection()
'adds apostrophes to numeric values only
Dim C As Excel.Range
For Each C In
Application.Selection.SpecialCells(xlCellTypeConstants).Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub AddApostrophesAllToSelection()
Dim C As Excel.Range
With ActiveWorkbook.ActiveSheet
For Each C In Intersect(Selection,
..UsedRange).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

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub
 
Have you tried clicking on the "Advanced" button just before the
wizard finishes and setting the data type to text there?
 
you're welcome :)
it doesn't really solve the problem, like John's solution does; it just
sidesteps it. but it's what i usually do - i despise importing from Excel!
<g>
 
Back
Top