Cell is truncated between two workbooks (97:SR2)

Topic

New Reply

Dear Excel Gurus,
I’m normally an Access person (please don’t hold that against me), but I have a problem with Excel workbooks that are linked together.
I have an Access database that uses TransferSpreadsheet to output a spreadsheet in Excel 97 format. Some of the fields in Access are memo fields and so are larger than 255 characters. The workbook that is output handles this correctly so that text greater than 255 characters is seen in that Excel workbook.
However, if I link a workbook to one of these large fields the linked cell is truncated to 255 characters in the second workbook.
Microsoft Knowledgebase has article 166691 that describes this exact problem. It states a resolution that all you have to do to circumvent the problem is to open the source workbook before or after you open the dependent workbook. I have tried this and it does not appear to work.
Does anyone have any ideas? Or any other circumventions?

Reply | Quote

Replies

Hi Bit_Meddler,

I think what you have to do to get the complete record is to have both the source and target workbooks open at the same time. You can then close the source workbook and the complete field will be retained – until the next time you update the link to the source workbook.

This isn’t terribly satisfactory, so you might need to explore another workaround. For example, if you can split the field across multiple cells in the source workbook, you could re-join them in the target workbook without too much difficulty and without having to worry about what happens when the links are updated.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks macropod,
As you said, it’s probably not entirely satisfactory to open both workbooks. Not only that, but Excel’s display of a large text cell ain’t very pretty.
I’ve looked at a workaraound as per your suggestion. What I’ve decided to do is create RTF reports from Access and output them to a number of files. Then, in Excel, create a number of buttons to display the reports. It does look a bit nicer this way and it works, too.
Thank you for your help.

Reply | Quote