Outputting to excel

[maeling]

I have a macro that outputs a table to excel.
The table has a field "objective" which is a memo field so it holds more than 255 characters.
However after I have output the table to an excel file and open the excel file some ofthe data is missing from this filed in the workbook in fact the cell in the workbook only holds 255 characters. Can you help ?

 

[addy]

Have you definitely checked that there isn't more information in the Cell? This may sound a daft question but the column width limit in Excel is 255 characters I believe so if your column is only displaying one character high then you will only see 255 characters.

Apologies if you think I am being too simplisitc but I have know users be fooled by this before.

 

[maeling]

Hi,
thanks for responding. Yes I'm sure that the data is missing after 255 characters. I've clicked into the cell, exteneded the cell and copied and pasted the cell contents into word then run a word count. I do wish you had of been right. I've got a feeling that his is going to be one of those threads that not many people have come up against.

 

[JerryKlmns]

maeling

it is the query that holds 255 characters. An excel cell holds about 37.776 characters.

 

[maeling]

JerryKlmns,
I'm not quite sure what you mean but the access table field holds more than 255 characters it is the cell in the excel output file that contains less.

 

[Binnit]

I believe there is fixed truncating at 255 chars using a memo field, I have come across this before when trying to format data within it. A97 help pages sort of ellude to your problem:-

Memo Text Simple conversion Data longer than the FieldSize setting is truncated.

Should I create a Text or a Memo field?

Microsoft Access provides two field data types to store data with text or combinations of text and numbers: Text or Memo.
Use a Text data type to store data such as names, addresses, and any numbers that do not require calculations, such as phone numbers, part numbers, or postal codes. A Text field can store up to 255 characters, but the default field size is 50 characters. To control the maximum number of characters that can be entered in a Text field, set the FieldSize property.

Use the Memo data type if you need to store more than 255 characters. A Memo field can store up to 64,000 characters. Memo fields can't be indexed or sorted. If you want to store formatted text or long documents, you should create an OLE field instead of a Memo field. To find out more about OLE fields, click .
Both Text and Memo data types store only the characters entered in a field; space characters for unused positions in the field aren't stored.

If IT ain’t working Binnit and Reboot

 

[maeling]

Hello,
thanks a very helpdful thread. It needs to be a memo field the users are inputting between 20 - 500 characters on average however when they are sending the infomation in via the output file only 255 characters are visible.

I'm not quite with you on your suggestion could you explain again ?

 

[JerryKlmns]

maeling

Since you are posting in Microsoft: Access Queries and JET SQL Forum I thought you were using a query. It is true that you can use a MEMO field to store more than 255 but when exporting this way you get the first 255 even from a MEMO field. There are treads in forum705 dealing with this problem using VBA ....

About excel, I've tested for Excel2000 and the exact value is 32767.

 

[maeling]

jeryyklmns

the end solution will involve a query however at the moment I am just testing with a table. Do you know of any threads in forum705 dealing with this particular problem ?