#1
|
|||
|
|||
Elegant / better solution to mail merge file cover problem
Hi all,
I'm returning to a problem I've been working on and off with for a while or so, convinced that there is a better way to do what I'm looking to do, so hopeful someone here is a bit smarter than me. I am looking to produce a standard file cover, as per the attached example which contains all the usual items a file cover would need. We currently have a mix of similar but different ones on the go between 15 people so I would love to get some standard and save people some time. The first attachment is the word document I wish to use, the second attachment is an excel sheet, which contains the 'mail merge data'. The sheet has three tabs. ApplicationSheetData which is where the data is entered, it contains a VLOOKUP formula which reads the entered reference number and returns the relevant information (highlighted red on the page) CMS_Codes, the reference list for the Vlookup in the first tab, Mail_Merge is the final tab and simply copies information from ApplicationSheetData and is the source of the mail merge linked to my word document. The first problem is that if a particular data line does not return info, for example an address line, then a zero shows rather than a blank line. The second problem is that when the merge runs if one of the lines of data is too long it carries over in the word doc and cannot be read. I have tried 'wrapping' the box but without any luck. My final problem is the biggest in that despite my best efforts the whole thing feels a bit convoluted and long winded. Is there anyway that the excel components can be incorporated in word in some way?? By this I mean would word be able to recognise the reference number and carry out some sort of VLOOKUP equivalent which is currently taking place in Excel??? Any help or tips to simply this thing will, as always be greatly received. Thanks for reading, Ballst |
#2
|
||||
|
||||
For the first problem, the simple solution is to merge the cells containing the address into a single cell. Then, assuming address fields 2-4 may or may not have data, you'd put them all on a single line with address field 1, thus:
«Address_Line_1»«Address_Line_2»«Address_Line_3»«A ddress_Line_4»↵ «Address_Line_5» «Postcode» where ↵ is a manual line break to keep «Address_Line_5» «Postcode» on a separate line. Then for each of address fields 2-4, you'd use the mergefield \b switch to conditionally output line breaks when those fields have data. To do that • select a given mergefield (e.g. «Address_Line_2») and press Shift-F9 to expose the field code, which will look like: {MERGEFIELD Address_Line_2} • edit the field code so that you get: {MERGEFIELD Address_Line_2 \b "↵ "} where ↵ is a manual line break. That's all there is to it. Now, when you merge, only the lines with data will be output. As for the second problem, you only need to switch off the 'fit text' and 'wrap text' options, which you've added to some cells. You also need to delete the extra paragraph marks from those cells and give the rows concerned an 'Exact' height setting, rather than the present 'At Least' setting.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks again Macropod,
Having spoken to folks who'll be using this form I've gone back to the drawing board a bit and am using a form fill in field, to populate the details, as each one is unique a mail merge is not totally suitable. The sticking point is the reference number, I'm still looking for word to identify part of it and populate some fields as a result. |
#4
|
||||
|
||||
Using formfields imposes severe restrictions on editing any other content in the documents concerned. As for the reference number, if you provide more details about its content and what you want to do with it, further advice might be forthcoming...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Ok, so here goes....
The reference number is the URN generated by our case management system (which should also have been designed to produce a cover sheet....) The reference number 22 characters long and is broken down into a number of differing components and would look like this; LON/00AJ/OLR/2015/0300 For this problem I'm looking for Word to be able to identify the bold highlighted part of the URN and return information in another field. For our example OLR would populate three fields within the word document with the following information; Field 1, New Lease of Flat, Field 2, Leasehold Reform Housing and Urban Development Act 1993 and Field 3, Section 48 There are 122 differing codes and they are detailed in the 2nd tab of the worksheet attached to my first post. |
#6
|
||||
|
||||
OK, Word's field coding can't parse a string like that. An on-exit macro could, though. Before investing any effort into that, though, please confirm whether you're using formfields or content controls and where the output should go. An updated sample document would help, too.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Hi Macropod,
Apologies for delay in reply, have been on a couple of weeks leave. I'm using formfields and as requested I've attached an updated sample document, which comes in two parts. On page one the document as a user would find it, and on page two how it would hopefully look. I've included some colour coding to help identify what is needed and where. At the top of the document, the Green highlighted text is where the user would enter the URN. The Red highlighted part of the URN is the bit I need Word to identify. The outcome/output is then displayed in the Yellow highlighted text below, for these I have added the field titles to the right of each. The text highlighted in Blue will just be a copy of the reference number entered at the top of the document. The codes are attached in the accompanying Excel sheet. Should it prove too difficult to identify that one part in the reference I guess the identified could be entered separately?? Thanks, Ballst |
#8
|
||||
|
||||
Hi Ballst,
Your document doesn't actually have any formfields; it has a mix of Mergefields, ASK fields and Fillin fields. From the way you have your «Case_Ref_Number» mergefield is depicted, it looks like you're trying to use it for cross-referencing the Fillin field used to solicit the Case Reference Number. That is not how mergefields work. Moreover, the table rows where you're soliciting the address details have the FILLIN fields duplicated (e.g. { FILLIN "Enter line one of the address" \d "Click Here" \o \* MERGEFORMAT }{ FILLIN "Enter line one of the address" \d "Click Here, then right click, select Update Field" \* MERGEFORMAT }). The first of these would prompt once in a mailmerge; the second would prompt for every record. Overall, though, I have no idea how you're trying to use this document. The content isn't consistent with either a mailmerge or a form that one would fill in.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Outlook mail merge w/attach and cover page | COGICPENNY | Outlook | 0 | 01-08-2013 01:41 PM |
Mail Merge Problem | Hook | Mail Merge | 2 | 01-11-2012 09:36 AM |
Cover page problem | bluegum87 | Word | 2 | 08-02-2011 09:43 AM |
Mail Merge Problem | JohnRG | Mail Merge | 0 | 11-02-2009 02:50 AM |
problem in Mail merge | rjagathe | Mail Merge | 0 | 08-12-2009 08:25 AM |