Linking to Excel Spreadsheet, Format Problems

[Brian Halman]

I'm currently linking to an Excel Spreadsheet which
contains our shipping report. THe link is fine with the
exception that fields in the report generated by Access
have different properties (Excel is formated as all text
with the exception of the date. Access is interpreting a
field that is the item # being shipped as a number. Text
in this field shows up as #Numb)which apparently cannot
be changed. The reason I'm linking is that this will
allow easier update of the shipping/receiving manifest.
We have tried to change formats in both Excel and Access
with no success. When the data is imported, we have no
format problems. Does anyone have an easy fix other than
deleting the current table and importing the same data on
a daily basis into a table with the same name?
Thanks for your help!

 

[John Nurick]

Hi Brian,

Excel doesn't have field types the way Access does, only cell formats -
which Access basically ignores when linking or importing. Instead Access
decides on the field type to use for each Excel column by examining the
first dozen or so rows.

When importing, it imports a column to a text field if there is a single
non-numeric value in the first rows. When linking, it links the column
to a number (double) field if there is a single number value in the
first rows (regardless of whether Excel has formatted it as text).

This seems crazy to me, but it also seems to be the way it is. To get
round it, you must make sure that there are no numeric values in the
columns you want to link as text. One good way of doing this is to put
an apostrophe in front of each value. This forces Excel and Access to
treat it as text. The apostrophe only appears in the formula bar, not in
the worksheet or in Excel VBA properties and not in the linked data in
Access.

These little Excel VBA functions will add and remove apostrophes as
required:

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