Why does the data type change when import/link an excel worksheet?

[Guest]

I am hoping someone out there can help me. I importing excel spreadsheets
into an access database. The excel files will be updated monthly so I am
linking them. However, when I do link them, for some reason the data type
changes and I get mismatch errors when I run queries. I know for a fact that
before I link the sheets, I change the cell format in excel to text. But
once I get it to access it is in Number format. I have tried changing the
format in excel after the file is linked but the excel sheet already says
that it is in text format. The only thing that works is to import the file
without linking it so that I can change the format after it is in access.
This will be a huge pain because I will have to do this every month and
rewrite all of my queries and realtionships. Is there anything I can do to
keep the format from changing?

 

[John Nurick]

Hi Perplexed,

This is a regular source of frustration. The Jet database engine used by
Access applies subtly different rules when linking and when importing;
either way it pays virtually no attention to Excel cell formats.

When the problem is a column with a mix of numeric and non-numeric
values, a reliable fix is to prefix the numeric values with an
apostrophe, e.g.
'1234
instead of
1234

This forces both Excel and Access to treat the value as a string of
digits and not a number. The apostrophe doesn't show up in the workbook
(only in the formula bar) or in Access. This Excel VBA procedure will
add apostrophes to numeric values in selected cells:

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