Solved

Mail Merge Generated Document - How to keep field in a paragraph

Posted on 2013-05-14
11
515 Views
Last Modified: 2013-05-21
Hi.

Pursuing an idea to generate some documentation via a workbook and have hit a small curiosity I don't know how to fix.

In the Word document (merge template) I have a paragraph under a table that is designed to be Table 1 ... then (automatically) Table 2 ... under the next table and so on.

If I copy/paste the template table & paragraph it works fine.

However, as soon as I generate a new document from the template that field in the generated paragraphs reverts to plain text and I've lost the ability for it to auto-number itself.

What do I need to do to retain this auto-numbering capability.

sample workbook (with macro/template) attached (.xslm)

follows on from:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28127039.html

Word & Excel 2010:
Make-Docs-From-Embedded-Template.xlsm
Q-28128386.png
0
Comment
Question by:PortletPaul
  • 6
  • 5
11 Comments
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 39167484
From the start, most of Word's features (macros, styles, bookmarks. fields, etc.) have been there simply as aids to producing a well-formatted printed document. The output from a mail merge is expected to be the end of the production process, so all bookmarks are dropped, and most fields converted to fixed text.

This is not a problem if the destination is fax, printer or email, but the possibility of outputting to another document does raise expectations. You would either have  to use a VBA emulation of mail merge, or do some post-merge work (manual or programmatic) to meet those expectations.
0
 
LVL 48

Author Comment

by:PortletPaul
ID: 39167542
right, I don't consider mail merge to be the end of the chain here, just part of the equation

so, either I emulate mail merge to build multiple tables in a loop... yuk

or 'post process', so the desire now is, what macro magic would match the style of these paragraphs and re-insert the 'field' for auto numbering behaviour?

a sample would probably be sufficient - I can fix the details
0
 
LVL 48

Author Comment

by:PortletPaul
ID: 39167557
maybe I need to rethink my attitude to using Mail Merge?

any recommended sample macros that would repeat a (table+paragraph) structure to a new document?

I do have another need to reproduce rows of a table that Mail Merge does not do very well (it wants to repeat the table headings).

mmmm
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 500 total points
ID: 39167630
We don't know your wider objective, but it sounds as if you might find building blocks useful.
You can create the fully formatted table and field, select it and save it as a 'Quick Part'. Then you can run some code like this which will add another such block at the end of the current document.
Private Sub InsertBB()
Dim objTemplate As Template
Dim objBB As BuildingBlock

Set objTemplate = Templates(1)
Set objBB = objTemplate.BuildingBlockEntries("TablePlusTitle")

objBB.Insert ActiveDocument.Bookmarks("\EndOfDoc").Range
End Sub

Open in new window

0
 
LVL 48

Author Comment

by:PortletPaul
ID: 39167677
rudimentary I know, but the broader picture was hinted at in the earlier question...

The overall objective is to help drive accurate documentation that evolves over time with numerous cross-references, from requirements to test plans and operational instructions

This is performed by a broad geographic spread of individuals (and translations are required)

The concept (and that's all it is right now) is to maintain all (or most) of the cross-referencing material in a workbook instead of Word docs - then derive parts of the needed Word docs when it is logical to do so. Ideally there would be some more robust toolset to undertake this - but there isn't - so it's the ubiquitous Excel/Word I'm resorting to.

The starting point is that I do not want to commence or control this from Word - it must be controlled from the Workbook - and Word elements generated from it.

nb: Not whole (final) documents are intended from the Workbook - just the numerous tables (with cross-references) that can be merged (manually) into the manually prepared deliverable documents.

appreciate any guidance really - right now this is conceptual - I want to build enough to be a 'proof of concept' really. samples and/or good references are always welcomed - I'm not expecting vba scripts of several 1000 lines :) - well not on the basis of one question anyway.

Not sure if this helps at all.

back to a question - where would the 'Quick Parts' be stored? and how are they referenced? (looks like I get to name them). Would these (or could these) be stored in a single embedded word doc?



ps: it's been many many years since I delved into VBA, but I'm not afraid of it, and once I get into the swing of it could be considered better than beginner (well I hope so).
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 500 total points
ID: 39167739
It might be worth assembling a structured document for use as the mail merge main document and then executing the merge.

With or without mail-merge, you will almost certainly need some VBA code.

If you need any help with code is solely or largely Word-orientated, can you remember to add Word to the relevant Zones? It might help to get more Word experts interested.
0
 
LVL 48

Author Comment

by:PortletPaul
ID: 39167762
I did include Word as a topic - didn't I?

Notwithstanding:
>> I will stress - the control of execution MUST be from Excel, NOT Word... on this I will not bend.

My additional desire is to embed any Word files(s) required into the Workbook so that versioning and distribution are simplified
e.g. v1 workbook has v1 embedded document and can survive on its own merits after v1.1 is issued and so on.
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 500 total points
ID: 39167970
Yes, sorry. I was looking in the wrong place.

In VBA, you can work on documents (including workbooks, powerpoint presentations, outlook items &c) belong to any Office application from any other Office application.

The Word user interface only allows saving a selection as a Quick Part to a template. I'm pretty sure that can be worked around, e.g. renaming the template as a document later. There isn't a fundamental difference between a document and a template - just name and usage. However, if mail merge is to be abandoned at the initial stage, embedded templates would be more logical.

Actually, you might be able to use one template only, containing all the blocks, but with graphic images of what each button will add, positioned where the document objects currently are.
0
 
LVL 48

Author Comment

by:PortletPaul
ID: 39171246
thanks for the tip on 'QuickParts' to be frank that entire piece of Word had escaped my attention. This proved to be an amazing reference on that topic:
http://gregmaxey.mvps.org/word_tip_pages/building_blocks_autotext.html

But, I now have  another concern. If QuickParts are stored in the Normal.dot or Building Blocks.dotx than these could be really troublesome for my intended purpose (broad distribution without complex dependencies).

Additionally, while that URL above provided some VBA related to QuickParts, I still haven't seen one which:
a. chooses a quickpart, and
b. uses that quickpart like a mail merge template (i.e. repeats the quickpart filled-in with data from Excel)

Do you really believe defining quickparts can work via an embedded document in Excel
(and used by someone half-way around the world)?
0
 
LVL 48

Author Closing Comment

by:PortletPaul
ID: 39183302
I'm not at all sure if quickparts will survive within a document embedded into Excel, but at least i now know something about their existence  thanks for that.

Thanks for the various pointers. I will still be working on this concept on  off for a while I think and will probably be back for more questions.

Cheers,
Paul
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39183752
Thanks & good luck
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now