What is the best practice when linking Microsoft® Excel® and Word documents in CCH® ProSystem fx® Engagement or Workpaper Manager?
Creating OLE links between Microsoft® Excel® and Word documents is a common practice. For the best results, the following recommendations have been written:
- When creating an OLE link between documents, customers should be using the Paste Link > Microsoft Office Excel Worksheet Object format.
- Other link options (such as Unformatted Text and Formatted Text) should not be used, as this creates a link that may not be as stable as the Microsoft® Excel® Worksheet Object.
- Worksheet tab names in Microsoft® Excel® should not contain spaces.
- Spaces in a tab name may cause the Named Range to be replaced with absolute cell references causing the OLE links not to expand when rows and columns are added in Microsoft® Excel®.
- After creating the OLE link, the Layout option of the link should always be In Line With Text.
- To check this, right-click on the link (in Microsoft® Word), and select Format Object. Look at the Layout tab, and make sure that the Wrapping style is set to In Line With Text.
- After creating the OLE link, do not resize or move the link.
- Resizing or moving the link will cause the Wrapping style setting to change, resulting in an unstable link.
- All sizing and formatting should be done within Microsoft® Excel® prior to linking to Microsoft® Word.
- To make the OLE links create with the proper Wrapping style, set the default link insert format to In Line with Text.
- To do this, select File > Word Options > Advanced. In the Cut, Copy, and Paste section, set the option for Insert/Paste Pictures as to In line with text.
- Do not embed Microsoft® Excel® objects.
- This process is done by selecting Insert > Object > Microsoft Office Excel Worksheet.
- This places a mini-spreadsheet into the Word document.
- This mini-spreadsheet has some basic Microsoft® Excel® functionality, such as formulas. However, no CCH® ProSystem fx® Engagement or Workpaper Manager, or TB Link functionality is available within this area.
Note: When converting documents from DOC to DOCX, it is important to understand that there are limitations that occur with OLE linking. When using the DOCX format, the options for pasting links as Formatted Text, Unformatted Text, and Unformatted Unicode Text cannot be used. The link locations will not be updated. It will cause errors on opening when the file is synchronized between users. For more information, see the article how do I manually convert .xls / .doc documents to the newer .xlsx / .docx format in Engagement.