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


"Jon" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

On 16 Aug 2005 17:48:06 -0700, "Jon" <(E-Mail Removed)> wrote:

>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


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

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



--
---------------------------------------------------------------
Michael J. Strickland
Quality Services (E-Mail Removed)
703-560-7380
---------------------------------------------------------------
"Jon" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

Thanks Tina, that worked.


Jon

Tina wrote:
> 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.

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>


"Jon" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks Tina, that worked.
>
>
> Jon
>
> Tina wrote:
> > 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.
>