Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-09-2015, 10:04 AM
ballst ballst is offline Elegant / better solution to mail merge file cover problem Windows XP Elegant / better solution to mail merge file cover problem Office 2003
Advanced Beginner
Elegant / better solution to mail merge file cover problem
 
Join Date: Dec 2014
Posts: 32
ballst is on a distinguished road
Default 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
Attached Files
File Type: xls Coversheet-PartA.xls (48.5 KB, 10 views)
File Type: doc CoverExample.doc (64.5 KB, 9 views)
Reply With Quote
  #2  
Old 07-09-2015, 05:43 PM
macropod's Avatar
macropod macropod is offline Elegant / better solution to mail merge file cover problem Windows 7 64bit Elegant / better solution to mail merge file cover problem Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 07-11-2015, 03:56 PM
ballst ballst is offline Elegant / better solution to mail merge file cover problem Windows XP Elegant / better solution to mail merge file cover problem Office 2003
Advanced Beginner
Elegant / better solution to mail merge file cover problem
 
Join Date: Dec 2014
Posts: 32
ballst is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 07-11-2015, 09:37 PM
macropod's Avatar
macropod macropod is offline Elegant / better solution to mail merge file cover problem Windows 7 64bit Elegant / better solution to mail merge file cover problem Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 07-13-2015, 11:16 AM
ballst ballst is offline Elegant / better solution to mail merge file cover problem Windows XP Elegant / better solution to mail merge file cover problem Office 2003
Advanced Beginner
Elegant / better solution to mail merge file cover problem
 
Join Date: Dec 2014
Posts: 32
ballst is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 07-13-2015, 03:27 PM
macropod's Avatar
macropod macropod is offline Elegant / better solution to mail merge file cover problem Windows 7 64bit Elegant / better solution to mail merge file cover problem Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #7  
Old 08-05-2015, 03:05 AM
ballst ballst is offline Elegant / better solution to mail merge file cover problem Windows XP Elegant / better solution to mail merge file cover problem Office 2003
Advanced Beginner
Elegant / better solution to mail merge file cover problem
 
Join Date: Dec 2014
Posts: 32
ballst is on a distinguished road
Default

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
Attached Files
File Type: doc Case1.doc (115.0 KB, 9 views)
File Type: xls FieldCodes.xls (28.5 KB, 8 views)
Reply With Quote
  #8  
Old 08-05-2015, 06:33 PM
macropod's Avatar
macropod macropod is offline Elegant / better solution to mail merge file cover problem Windows 7 64bit Elegant / better solution to mail merge file cover problem Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

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
Elegant / better solution to mail merge file cover problem Mail Merge Problem Hook Mail Merge 2 01-11-2012 09:36 AM
Elegant / better solution to mail merge file cover problem 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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:02 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft