#1
|
||||
|
||||
Advice please - too many merge fields!
Hi
I've been asked to help one of my colleagues who is going to be receiving what is essentially a relational database from a client, but as a flat file, to be sent a letter with accompanying pages of tables with billing info. So he will get 8 fields of an address, followed by 22 fields of billing info for each bill referenced for this address. The example we've got is for 1 address with 65 different bill references, so it's a single row of data with 1438 fields, which is clearly bonkers! What's worse, they want each of the bill references (22 fields) to be on separate line of a table in a document. As far as I'm aware, you can't set up a merge to a table so that it adds a row for each set of fields that is occupied - you have to define the rows 'up front'. We can't put in the maximum number of rows as it would mean a lot of blank rows for those records with only a few bill references. Obviously inserting 1438 fields is going to take an absolute age and is impractical in itself. Could anyone suggest the best way to approach this problem? - needless to say going back to the client is not an option as this is the only way they can send the data to us. I do know some VBA and vb.net, so could do some macros to prep the data before the merge. I'm still a little stumped as to the best approach though. Any ideas? ........(please!) |
#2
|
||||
|
||||
Update: I just found this post from Macropod which looks like the basis of an answer:
https://answers.microsoft.com/en-us/...f-8642e46fa103 I did some tweaking with the data and made it relational, then followed the post, so I've got the merge itself working now. Unfortunately I'm not able to control the format of the data in the usual way (adding \# "£0.00" to the MERGEFIELD) as it uses sql-type fieldnames, and Word uses (example) £1.9 instead of £1.90 if I try to format as currency in the xlsx data source. Can anyone suggest a way to tackle this issue? |
#3
|
||||
|
||||
OK, assuming I can;t reformat the merge fields I've set them up as text format in Excel and I'll have to tweak them from there.
Final query - is there a way of controlling the cell width? - only way I can see is by using a header, but I can't go with that option here. I can re-size, but it snaps back to whenever refreshed. Is there a way to do this from the \l \b switches? - I can't see one! |
#4
|
||||
|
||||
This sounds like a many to one mail merge for which I suggested a solution from my web site in your linked post.
That add-in allows the use of macros, with which it is a simple matter to format the currency values in the table. As that uses a table with one data row that is duplicated as required, you can set the cell widths by making the table cells fixed width. You can use a landscape orientation to get more cells in the column.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#5
|
||||
|
||||
Yes it is a one-to-many, I'll check out the link.
Thanks! |
Tags |
merge data |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mail Merge fields merge incorrectly and won't fix. | marym | Mail Merge | 3 | 04-09-2016 05:17 AM |
Displaying FORMTEXT fields when merge fields are empty or null | sfkHooper | Mail Merge | 6 | 01-19-2016 04:24 PM |
merge fields disappear in merge doc but prints | mdod | Word | 1 | 07-19-2013 06:33 PM |
Conditional merge fields in mail merge | Aude | Mail Merge | 1 | 01-06-2012 07:38 PM |
Need advice on category sorted e-mail merge | GeoB | Mail Merge | 3 | 09-08-2011 11:03 PM |