Export and Analyze With error

[NPDPallotti]

Hi,

I'm trying to copy and paste information (one row from a database into one
Excel spreadsheet at a time, to be saved as unique spreadsheets of one row
each, which will be edited by external clients and then pasted back into an
Access database) from Access into Excel. Each time I try, data is missing.

I tried exporting the data from Access to Excel using File>Export as well as
Analyzing the data with Excel using Tools>Office Links>Anaylze it with Excel.
Each time, I get the same problem: the data in certain fields cuts off at
255 characters. In the original database, I have these fields set to Memo
rather than text to avoid the 255 cut-off.

Any ideas on why Access is cutting off the data when I try to put it into
Excel?

Thank you!

Stacie

 

[John Nurick]

Hi Stacie,

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 Excel 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) or a field generated by some other expression.
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.
See e.g. http://support.microsoft.com/default.aspx?scid=kb;en-us;207668