I searched for some help on this but couldn't find anything similar. I hope this is the right forum for this question.
I have an Excel app that opens a Word document which then copies the contents of the last selected cell from Excel to the document, and has a macro to store the text back in the cell when it is done. I have two issues:
1) There appears to be a difference between the CRLF in Excel and Word. When I store text from Word into an Excel cell that contains paragraph marks created while in Word they appear as rectangular boxes in the Excel cell but do not cause a carriage return line feed the way the "Alt-Enter" combination does. When I edit a cell in Excel with these boxes and interject "Alt-Enter"s there they appear, by their action and presentation, to be different but I created a series of Code statements to examine the characters in the cell and they all return a character code of 13 (consistent with the code I would use in Word VBA code to cause a CRLF). When the text is copied to Word they act like regular paragraph marks. and get converted to the rectangles when stored back in the Excel cell. I have attached two files; the workbook and a template. If you save them to any folder (but they have to both be in the same folder) they will work as long as you have only this one workbook open (no other instances of Excel or other workbooks open). Open the workbook and review my other comments below. Then let the macro attached to the "Edit" button create a Word document from the template to experiment. There is no real need to open the template directly. Cell A4 shows a comment with 4 "Enter"s after it. Cell A6 shows 4 "Enter"s I created in the Word doc and then edited in Excel to add "Alt-Enter" after the second, third, and fourth one. You can see that they look different but A8-A13 show they all have a code of 13. My machine is so slow that I can see this cell change in the Excel formula bar to 5 rectangular boxes while the macro is running prior to the Word window coming up but I don't know what that means. Does anybody know what is happening here? I'd like to have the paragraph marks in Word turn into "Alt-enter"s in Excel so as much of the comment as possible appears the same in Excel as it does in Word.
2) I'd would love to hear from anyone who knows a better way to do this but so far this is the only way I have found to address some issues my users are complaining about. One issue is a cell being able to hold 32,767 characters but display only 1,024. Even that is sometimes cut off because the autofit row height doesn't expand enough to show the whole 1024 characters. Another is the spell checker but I found an article that would allow the use of the Word spell checker in Excel. The user has asked for a "Word or Word like window to edit the text" and I'm trying to accommodate. I'm even open to using another application.
Thanks for any help or ideas you can offer!