Problem using mixed data from Excel

[Tom van Stiphout]

Office 2003 (but may also occur on others).

Create new Excel file. Enter a column of data like this:
Column1
aaa
bbb
ccc
ddd
eee
111
222
333

Create a new database and create a linked table to this workbook.
Open the table view.
Observe:
aaa
bbb
ccc
ddd
eee
#Num!
#Num!
#Num!

Check the table design and observe the data type for that column is
Text255.
Why is Access too stupid to display the data? Obviously in normal
Access (Jet) tables a Text255 column can have a value of "111".
One work around is to "prep" the Excel file by putting a single quote
in front of all numeric values. Ugly!
Any better ideas?

Thanks,

-Tom.

[Dean]

The problem is Excel, not Access. Take your Excel and save it as a csv
then link to that in Access. Or import the Excel spreadsheet and
define the field as text. It'll work.

The problem: Excel is saving a number as a number. Since it is not
field driven, it designs each cell independently. That is one of the
fundamental differences between a database and a spreadsheet.

[Lyle Fairfield]

Set tdf = DBEngine(0)(0).TableDefs("Sheet1")
tdf.Connect = Replace(tdf.Connect, "IMEX=2", "IMEX=1")
tdf.RefreshLink

[Tom van Stiphout]

On 11 Jan 2006 17:53:48 -0800, "Lyle Fairfield"
<ly***********@aim.com> wrote:

10 points!
I added this code to the reattach logic, renamed it to LF_Akbar, and
it worked.
Thanks a bunch.

-Tom.

Set tdf = DBEngine(0)(0).TableDefs("Sheet1")
tdf.Connect = Replace(tdf.Connect, "IMEX=2", "IMEX=1")
tdf.RefreshLink

[Tom van Stiphout]

On 11 Jan 2006 17:45:34 -0800, "Dean" <de**@coveyaccounting.com>
wrote:

Thanks. If Lyle hadn't made his great suggestion, that's exactly the
route I would have taken.

-Tom.

The problem is Excel, not Access. Take your Excel and save it as a csv
then link to that in Access. Or import the Excel spreadsheet and
define the field as text. It'll work.

The problem: Excel is saving a number as a number. Since it is not
field driven, it designs each cell independently. That is one of the
fundamental differences between a database and a spreadsheet.

[Lyle Fairfield]

LOL!
Glad it helped.