Problem exporting report to Excel

[Dakas]

While exporting MS Access 2000 report to Excel, text fields are cutted up to 127 characters (instead of used text(255)).
In report preview everything is OK.
How to solve this problem?

Urgent help needed.
Thanks in advance.

 

[Garridon]

This is caused because Access exports as an Excel 95 file, and it can only export so many characters. Even if you choose to export it as 2000, 2002, it still exports it as 95. Exporting directly from the table or a query doesn't make any difference.

There is a way to fix it, but it's really, really messy:

Create a report for export. Put an unbound field on the report. Insert the following into the unbound field:

=Mid([txtFieldName],1,127)

The numbers refer to the start and end point of the characters (Microsoft says that it should truncate after 255 characters; however, it may also truncate after 127 characters).

Repeat the above process until all the characters are accounted for. The next field, for instance would be 128, 254.

After creating all the fields to split apart the text field, create a blank unbound field. This will export as a blank column in Excel.

Export the report.

In the blank field, insert the following formula:

=Concatenate(E2,F2)

...substituting in the appropriate cell numbers. Concatenate puts it back together again.
Linda Adams
Linda Adams/Emory Hackman Official Web site

http://www.hackman-adams.com

Official web site for actor David Hedison:

http://www.david-hedison.com

 

[Dakas]

I have already solved that problem in such way:
added ID into report, exported report into Excel and than connected to dbase and imported text fields directly from excel.


Anyway, Garridon, thank You very much.