Linked Excel File

[Guest]

Data in Excel Field = U6. I format the Excel Field as General but when I link
the Access db, it identifies the field as a number and does not present the
correct info. I tried formating the Excel Field as "Text" as well with the
same result.

#Num! displays in the cell.
Field type is double and format is General Number under design view.

I cannot change the Field Format in Access as it is to a linked table and
won't let me.

Any Suggestion would be appreciated, thanks in advance and we all should be
celebrating instead of working. Maybe in '06.

 

[Ken Snell \(MVP\)]

A commonly asked question....

The problem is that Jet is seeing numbers in the first 25 or so rows of your
EXCEL spreadsheet, so it assigns a number data type to that field, and then,
if it finds a text value in the field in a later row, it errors and won't
properly display that value.

You can work around this by adding apostrophes to the beginning of the
number values in the EXCEL cells (which tells Jet that the numbers in fact
are text), or you can put a text value (such as what you posted) in the
first row in the EXCEL sheet.

There are ways to make changes in the registry to force Jet to examine all
rows before deciding on a data type, but that probably is overkill for what
you want to do right now. Here is a link to an article that discusses this:
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

 

[Guest]

First, thanks for such a quick reply. I'll review the article but wanted to
note this to make sure I'm reading you correctly:

When I saw what was happening, I went back and made the First entry below
the Header "U6" assuming something similar to what you describe was
happening. It did not change the outcome. If you have an idea on why, I'd be
interested in reading it.

Thanks.

 

[Ken Snell \(MVP\)]

Jet handles linked EXCEL data differently at times than it handles the
importation of EXCEL data. I have often found that I must import the data
from EXCEL, using the trick that you have tried, in order to get the data to
be properly recognized. Linking does not allow any way to modify the data,
unfortunately, and you've discovered that the "trick" is not always
successful with linked data.

 

[Guest]

Hi Michael,

In Excel, select column U so that the entire column is selected. Click on
Format > Cells. Select the Alignment tab. Set the Horizontal format to
General. Any text values should now be left aligned, whereas numeric values
will be right aligned.

Then try using Excel's Text to Columns function to see if that helps resolve
this issue. With column U still selected, click on Data > Text to Columns...
Accept the defaults as a first test: (Delimited, Tab, & General Column
format).


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Data in Excel Field = U6. I format the Excel Field as General but when I link
the Access db, it identifies the field as a number and does not present the
correct info. I tried formating the Excel Field as "Text" as well with the
same result.

#Num! displays in the cell.
Field type is double and format is General Number under design view.

I cannot change the Field Format in Access as it is to a linked table and
won't let me.

Any Suggestion would be appreciated, thanks in advance and we all should be
celebrating instead of working. Maybe in '06.