Wierd Excel Text Problem

[EliseFreedman]

Hi There.

We have come across a very strange problem which is baffling me and I wonder if anyone can point me in the right direction to solve it.

We have an application which has a facility to save queries as excel files. We all use this facility most days. It has been drawn to my attention by one particular user that when he outputs from a query to excel, it is cutting off some of the text in excel. I have run the exact same query on my PC and output it to excel and it exports fine with all the text being intact. I got a third member of our department to try running and exporting her query and it worked fine on her PC too. This makes me think it is a problem with his excel but I have no idea what to look at. Can anyone give me any hints on what the problem could be?

Elise

 

[Gruuuu]

Can you give an example of the text that is being retrieve, both successfully and unsuccessfully?
Also what kind of database are you connecting to?

 

[kjv1611]

What text is being cut off? Rows? Text within cells? As Gruuuu said, we need more info.

If it's Rows, is the user with the issue using Excel 2007 or an older version of Excel? Are you and the other user who had no issues using Excel 2007 or an older version? Excel, prior to 2007, had a limit of some 66 or 67k rows (you can Google for the exact number if you need it), but Excel 2007 has moved that limit up to something over 1 million... I believe something like 1 million 300k or 1 million 200k, I forget - Google is your friend there as well, if you need exacts.

The point is that if one person is using Excel 2007, they can export more rows than someone using a prior version.

 

[EliseFreedman]

It is text within cells that is being cut off.

For example in our database (which is based on intersystems cache) the description is
"I.P was putting hangers and U Bolts onto pipe so that they could tack them all up but one of the hangers was not straight so i.p hit it with a hammer to straighten it up thats when he felt something hit his eye but i.p said it was ok and did not feel anything until later that evening i.p said he was wearing his safety glasses at the time."

When I run the query I get the entire description as above. When my colleague with the issue runs the query his description is

"I.P was putting hangers and U Bolts onto pipe so that they could tack them all up but one of the hangers was not straight so i.p hit it with a hammer to straighten it up thats when he felt something hit his eye but i.p said it was ok and did not feel anyt"

 

[EliseFreedman]

Sorry, Meant to say we are all using Excel 2003 with service pack 3 installed

 

[GlennUK]

Hmmm that's 255 characters. Is your colleague doing anything to the query results prior to viewing them ( like copying the sheet elsewhere )?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

I think I recall that if the sheet is copied, any cell text is truncated to 255.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Just to add with respect to the 255 character limit and my hard earned experience:
You can copy paste cells without an issue.
You cannot copy a worksheet without losing the text in excess of 255 characters (there is a warning message).
You can move a sheet to a new workbook say and it will NOT lose the characters.
If using vlookup to pull in data from a closed workbook then only the first 255 characters are pulled in.
If using vlookup to pull in data from an open workbook then ALL characters are pulled in.

There are circumstances when wrapping text within a cell stops at 255 characters - so the remaining are in the cell but not visible.

All this may well be dependent on excel version.
Lesson: Avoid exceeding 255 characters if you can. If not test VERY carefully and be prepared for some update to cause a change in behaviour.

Gavin

 

[PRPhx]

I copied/pasted your text into an Excel 2003 spreadsheet. All of the text did copy, but with ################ instead of the text. So I did the following:

1) Make sure you format the cell as General (Under the numer tab from the Format menu).

2) Under the Alignment tab, click on wrap text.

3) Adjust the cell width and height so that the text is presentable.