Format for Linking Excel 02 data to Access 02

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Excel file where one column contains Quotation Numbers (text). Entries are generally 4 digits, however sometimes a range is entered (XXXX - YYYY). When I link to the Excel file, the Access wizard views the data as a numeric field thus producing a #Num! error for those entries that are ranges. I've tried formatting the Excel cells as text and changing the field data type to Text (it apparently can't be modified).

Can you offer a suggestion here?
 
When ACCESS links to a spreadsheet, it ignores the formatting of the cells
and instead decides on its own what is the "correct" format, based on the
initial rows. Thus, ACCESS can decide that a column in the spreadsheet is
numeric even though there are alphanumeric characters farther down.

Try importing the spreadsheet; ACCESS often handles the formatting
differently. Or you can add ' character to the front of the cells' values
that are numbers; that tells ACCESS to treat them as text.

Or, worst case, use Automation via VBA code to open the spreadsheet and read
it cell by cell and write it field by field into a recordset variable that
is based on a table that is to get the data.

--
Ken Snell
<MS ACCESS MVP>

Jon said:
I have an Excel file where one column contains Quotation Numbers (text).
Click to expand...
Entries are generally 4 digits, however sometimes a range is entered (XXXX -
YYYY). When I link to the Excel file, the Access wizard views the data as a
numeric field thus producing a #Num! error for those entries that are
ranges. I've tried formatting the Excel cells as text and changing the
field data type to Text (it apparently can't be modified).
 
Back
Top