Create Examples of multi-document publication in Word / Excel

[Brian Lewis]

No description provided.

[Brian Lewis]

Creating a multi-part document allows multiple people to work on the document at once, without having problems of contention over a single file.

The basic approach is:

• Each chapter of the document is a single word document.
• Each chapter document is linked to a single corresponding Excel workbook to provide its data.
• Each chapter document sets a value for a Word sequence filed called chapter.
• The chapter is set like this: {seq chapter \r 5}, then referenced throughout the document like this {seq chapter \c} (where the {} represent a field code).
• The chapter is included in the document footer as part of the page number; like this:
  {seq chapter \c}.{page}
• The chapter number is included in the caption for each figure, like this:
  Figure {seq chapter \c}.{seq figure} Caption of this figure
• A master “publication” document contains the front ( and end) pages, together with all the table of contents, table of tables, table of figures etc.
• Referenced Document (rd) fields are included in the master publication for each chapter, like this {rd “c:\\digest\\2018\\chapter1.docx”}
• The tables of contents in the master document include all matching entries from every referenced document.
• Each table includes \s and \d switches, so that the page number shown in the table includes the chapter number: like this { toc \s chapter \d .}
• Note that other switches are available to fine-tune the text entry in the TOC: see the examples above and also:
  https://support.office.com/en-ie/article/field-codes-toc-table-of-contents-field-1f538bc4-60e6-4854-9f64-67754d78d05c?ui=en-US&rs=en-IE&ad=IE

[Brian Lewis]

The attached sample include a master document, 2 “chapter” documents, and a single Excel workbook ( normally there would be separate workbooks for each chapter).

‌

Links in MS Office use absolute paths. Therefore, for minimal fuss, put everything in a folder c:\statsdigest

‌

https://www.dropbox.com/s/2btj6frescvaso8/Pineapples Multipart Book Publication.zip?dl=0

[Brian Lewis]

For Office add-in to facilitate “publishing data”:

‌

https://www.dropbox.com/s/d290hxlbpd64sfg/PineapplesDPTSetup.exe?dl=0

‌

Documentation:

https://www.dropbox.com/s/p77dusqphqigiw7/DPT Manual.pdf?dl=0

[Brian Lewis]

But just a note on References. How would you handle those in a multi-part document approach? Could you create yet another part for the end with a page that references all the content pages and "see" all references in there hence available for automatic building of references? No need to look into this. But I do hope it would be possible.

[Brian Lewis]

@Ghislain Hachey A few comments re DPT Send To Word vs manual copy /paste link, this is largely in the documentation, but I'll draw your attention to it.

First off - to defend using Picture as the default link format !

• Picture, when linking to Excel, means you get a "Windows metafile". This is a vector format, not a raster format (bitmap gives you a raster image). In particular, any truetype fonts you have used in Excel are rendered as truetype fonts in the metafile. What this means is that you can scale it cleanly and get very good results. Of course, the key thing if you do scale is to keep the aspect ratio at 1:1; otherwise it will indeed look bad.
• To best judge the quality of the linked picture, Export to PDF and look at it in the PDF document, rather than go by what you see on the screen in Word. See the attached table Enrolment by Grade and Age in this linked PDF. This is a picture and has been fitted neatly. (I see this dropbox link opens in dropbox in the browser - select open with… to open it in e.g. Acribat DC to best see what it looks like.)

‌

Second point to note is what you actually link to. My preference is to create specific ranges for tables in Excel, and populate these from a pivot table using getPivotData (inbuilt Word command) or pvSum( DPT command. pvSum is more powerful, but you can only use it if you are only intending to use the workbook on machines with DPT installed).
This gives you ultimate control over layout, and allows you to include calculations in the table quite easily.

You can link directly to a pivot table; Send to Word in fact assists this by generating a link to the pivot table data body. If you do it this way, and the pivot table changes shape or size, the link will still point to the pivot table range. If you do it manually, you will continue to point to the original rows and columns, which may or may not be the pivot table any more. To get this effect, you Send To Word with the selection anywhere in the pivot table body.

Same holds for lists.

There is some variation in the formatting of links generated by Send To Word. Briefly:
a multi-cell range -> picture
a chart -> picture
a cell in a pivot table -> pivot table body as picture
a list -> HTML - this is to allow it to break across pages; it will also remember any heading rows you specify if you refresh.
a single cell -> text. This allows you to embed a data driven value in the text e.g. The sentence in the sample:
There are 2013 pupils enrolled in Kosrae.

Where the value 2013 is a single cell in Excel and so is linked as text.. (You can go further and use the DPT function strXToWordsX to generate a grammatically correct sentence, and convert the number to text)

A critical thing for re-usability is to ensure that any link to an Excel range links to a name. If you do a manual copy/paste link, you'll get a reference to a selection of rows and columns ie the link will say something like: $A$4:$d$6 Now if you add rows or columns or move the object in any way, this link is broken. Best practice to my mind is to give the range a meaningful name before you link it. But SendToWord will create a default name for the range if it does not already have one.

You should also rename charts to give them meaningful names.

Captions - I definitely agree these should be done as separate paragraphs in Word - with their own style, and decorated with {seq chapter \c}-{seq figure} as we talked about before. This is how it is done in the samples linked in this thread and is the key to getting the list of tables and list of figures in the publication document. Also - remove embedded titles from charts.

In summary, what we are after is the ability to reuse Word and Excel artefacts from year to year, being able to refresh the data , regenerate the Excel objects, and update the links to these objects in Word with minimal effort. This is the underlying motivation for why a lot of things in DPT are done the way they are. Have a look through the manual which covers a lot of this.

[Brian Lewis]

Aspect Ratio -

This document shows the same pivot table linked four times.

1. was done by hand - this shows that by default word introduces a small rounding error in aspect ratio

.

![](https://bitbucket.org/repo/EdnrM9/images/2751156733-image.png)

2. Done with Send To Word, which fixes the aspect ratio after the object is created. Subtle improvement.

   3. Is a copy of 2, but resized to 75%. Because the fonts are scaled in a metafile this still looks perfectly good. 4. Linked as HTML - Excel Source. This one is not the same as the Excel original - see the column header wrappings.

As above, open in Acrobat DC to best see what it looks like.

This is why i contend Picture is the best linking format:

• quality-wise its indistinguishable from text formats when printed to PDF
• its easily scaleable
• it matches exactly the presentation in Excel
• prevents page-breaks mid table

[Brian Lewis]

And here’s another interesting thing: when the linked Picture object is rendered in the PDF, the text is rendered as text, because you can select it:

ie this is not a bitmap image.

So you are still getting the best possible resolution of the table, using scaleable fonts.

[Brian Lewis]

A further note:

here I have changed the font in Excel:

In Word, i have removed the Link as HTML table. That means the only occurrences of the font Segoe UI Light

are inside the linked Picture objects. When I generate PDF from this (here) and look at the Document Properties in Acrobat DC - it shows that font is embedded. So, that font information is preserved from Excel, via the linked Picture object, into the PDF, where it is used to render the Excel table.

[Ghislain Hachey]

Thanks @Brian Lewis and more advanced users will be invited to look at this to further improve their skills and the workflow. However, many users on the ground may not be ready for this and a plain copy paste between excel might be the best for the time being. The important is that they are the ones doing this and own their own data and reporting. Refinements can come later. We’ll see during our various trainings when and if introducing this can be done.

[Brian Lewis]

In RMI the SendToWord option has already been used throughout, I can tell this by noting the generated range names on the linked Excel areas.

This is important to remember - lining to a range name is more reliable when refreshing a link than linking to an explicit Sheet and $A$1:$Z$1 style link which will break if the range moves on the sheet by insertions or deletions around it. So if working with a manual cut-and-paste this should be remembered, and whether or not you paste as picture or html you will require some fiddling with the object format (e.g. aspect ratio for picture, or column widths in HTML) ; in case of HTML this is likely to be required with every refresh of the link.

Also, a new installer for DPT is available at:

https://www.dropbox.com/s/6sxdtyqerarwnw9/DPTSetup.exe?dl=0

This one changes the tag to DPT, and seems to reliably do all the registrations necessary to make it immediately active in the host applications.

[Ghislain Hachey]

Thanks. I’ll test it and will introduce it in the training and will see how users do.