Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-03-2021, 04:26 AM
catflap's Avatar
catflap catflap is offline Advice please - too many merge fields! Windows 7 64bit Advice please - too many merge fields! Office 2013
Advanced Beginner
Advice please - too many merge fields!
 
Join Date: Aug 2015
Location: UK
Posts: 72
catflap is on a distinguished road
Default 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!)
Reply With Quote
  #2  
Old 11-03-2021, 07:33 AM
catflap's Avatar
catflap catflap is offline Advice please - too many merge fields! Windows 7 64bit Advice please - too many merge fields! Office 2013
Advanced Beginner
Advice please - too many merge fields!
 
Join Date: Aug 2015
Location: UK
Posts: 72
catflap is on a distinguished road
Default

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?
Reply With Quote
  #3  
Old 11-04-2021, 02:14 AM
catflap's Avatar
catflap catflap is offline Advice please - too many merge fields! Windows 7 64bit Advice please - too many merge fields! Office 2013
Advanced Beginner
Advice please - too many merge fields!
 
Join Date: Aug 2015
Location: UK
Posts: 72
catflap is on a distinguished road
Default

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!
Reply With Quote
  #4  
Old 11-04-2021, 04:11 AM
gmayor's Avatar
gmayor gmayor is offline Advice please - too many merge fields! Windows 10 Advice please - too many merge fields! Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
  #5  
Old 11-04-2021, 06:53 AM
catflap's Avatar
catflap catflap is offline Advice please - too many merge fields! Windows 7 64bit Advice please - too many merge fields! Office 2013
Advanced Beginner
Advice please - too many merge fields!
 
Join Date: Aug 2015
Location: UK
Posts: 72
catflap is on a distinguished road
Default

Yes it is a one-to-many, I'll check out the link.

Thanks!
Reply With Quote
Reply

Tags
merge data

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Advice please - too many merge fields! Mail Merge fields merge incorrectly and won't fix. marym Mail Merge 3 04-09-2016 05:17 AM
Advice please - too many merge fields! Displaying FORMTEXT fields when merge fields are empty or null sfkHooper Mail Merge 6 01-19-2016 04:24 PM
Advice please - too many merge fields! merge fields disappear in merge doc but prints mdod Word 1 07-19-2013 06:33 PM
Advice please - too many merge fields! Conditional merge fields in mail merge Aude Mail Merge 1 01-06-2012 07:38 PM
Advice please - too many merge fields! Need advice on category sorted e-mail merge GeoB Mail Merge 3 09-08-2011 11:03 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:52 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