Data type conversion from Excel

[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

 

[tina]

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

 

[John Nurick]

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?

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

 

[Michael J. Strickland]

Have you tried clicking on the "Advanced" button just before the
wizard finishes and setting the data type to text there?

 

[Jon]

Thanks Tina, that worked.


Jon

 

[tina]

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>