Transfer data to Excel

  • Thread starter Thread starter cameron pour
  • Start date Start date
C

cameron pour

We have a memo field with more than 1000 characters along
with other fields such as dates, numbers, text and so on.
We are trying to transfer data to excel to modify the
contents and then use it in MS Project. When we save the
file as a xls and open it in Excel , we lose all data in
memo field which is longer than 254 characters. When we
saved as a txt and then open it in Excel, it cut the
content of the field after 516th character and put the
rest of the field in another cell. Our question is if
there is a way to change the default value for length of
the cell in Excel to transfer whole content of a field to
one cell. We tried the delimited and fixed width options
and did not succeed.
 
Hi Cameron,

There are some restrictions on exporting memo fields with contents
longer than 255 characters to Excel without truncating them. As far as I
can make out they are:

-You need to be using Office 97 or later.

-You must export a table or query, not a report.

-You must export to Access 97 or later format.

-You must export using File|Export (without checking "Save Formatted")
or DoCmd.TransferSpreadsheet. Don't use "Analyze it with Excel" or
DoCmd.OutputTo.

-The memo fields must not be formatted (e.g. using the Format property
of the field to convert it to upper case). This causes them to be
treated Text fields on export and truncated at 255 characters.

-They can't be calculated fields (e.g. several shorter fields
concatenated together). These too are treated as Text fields when
exported. To get round this, you have to append the records to an Access
table and then export that.
 
Thank you John for helping us. We are using Office 97 and all fields are just values entered manually.

We finally could solve the problem. WE noticed that where ever we have a "Return Control" in the text , Excel sends the rest of text to the next cell. So, we copied and pasted those columns containing long text to Word and by "Replace" command erased all "Return Control Format Codes" and then copied from Word and pasted to Excel as column.
 
Back
Top