Char/Int data in same Excel Column

  • Thread starter Thread starter J. Shrimps Jr.
  • Start date Start date
J

J. Shrimps Jr.

Linking (not importing) an
Excel Spreadsheet with both
numeric and character data
in the same column. Either the
numeric data appears fine and the
character data appears as #NUM, or
vice versa.
Have formated the Excel column as
all text, didn't help. I don't
care if the integer data is read in
as text, I will append the column
to a table formated for integer.
How do I link an Excel spreadsheet
with mixed data in the same column?
 
Make sure that there are *no* numeric values in the first dozen or so
cells in the column. When you're importing, a single text value near the
top of the column makes Access import to a text field; when linking, a
single numeric value makes it a number field. This seems crazy to me.

If you stick an apostrophe ' in front of a number in a cell, Excel and
Access will both treat the number as text. The apostrophe doesn't appear
in the worksheet or the .Formula and .Value properties of the cell, nor
when you link or import to Access.
 
There are no values in the first hundred or so rows. I am
used to typing "TEXT" in the first row under the header.
Yes the char strings are read in correctly, but the integers
appear as #NUM. We thought of placing a ' in front of
each cell, but there are too many rows and the end user
updating the spreadsheet won't be that careful....
 
Putting a text value in the first row works when importing, but when
linking you have to do it in *all* of the first dozen or so rows. I
haven't experimented with empty cells, but it looks as if Access is
treating them as numeric for this purpose.

You can use Excel VBA code to add the apostrophes:

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

There are no values in the first hundred or so rows. I am
used to typing "TEXT" in the first row under the header.
Yes the char strings are read in correctly, but the integers
appear as #NUM. We thought of placing a ' in front of
each cell, but there are too many rows and the end user
updating the spreadsheet won't be that careful....
Click to expand...
 
Back
Top