By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,776 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,776 IT Pros & Developers. It's quick & easy.

Problem using mixed data from Excel

P: n/a
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.

Jan 12 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.

Jan 12 '06 #2

P: n/a
Set tdf = DBEngine(0)(0).TableDefs("Sheet1")
tdf.Connect = Replace(tdf.Connect, "IMEX=2", "IMEX=1")
tdf.RefreshLink

Jan 12 '06 #3

P: n/a
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


Jan 12 '06 #4

P: n/a
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.


Jan 12 '06 #5

P: n/a
LOL!
Glad it helped.

Jan 12 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.