Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Mail Merge

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 04-03-2017, 12:03 PM
Kokiri Kokiri is offline Windows 8 Office 2016
Novice
 
Join Date: Apr 2017
Posts: 4
Kokiri is on a distinguished road
Default Mail Merge: Converting Text to Table

Hello!



I am trying to set up a mail merge from a database. I wasn't sure whether to post here or in the macro thread, but I opted for here because maybe a macro isn't the best way to accomplish what I need. I read a lot of Macropod's responses on various websites, and he has helped me with some other problems I was facing.

But in this case, I have a field pulled from a database that looks roughly like this.

^Total^Fall^Spring
Award 1^$2,000
Award 2-Award^$1,000
Award 3 etc.^$500

I then made a macro that pulls apart this field and formats it as a table without borders. (Minus the underscores)
____________________Total_______Fall_______Spring
Award 1_____________$2,000_____$1000.00___$1000.00
Award 2-Award_______$1,000_____$500.00____$500.00
Award 3 etc._________$500_______$250.00____$250.00
Total_______________$3500.00____$1750.00___$1750.0 0

All of this works great. The problem is, that when the mail merge field is unlinked, it overwrites all records with the first record's data. So all of the letters besides the first will display incorrect information. I'm leaning towards a table if possible, because as you can see, my macro performs some calculations with this data. It will be easiest to access in a table.

Is there any way to convert the text of the field to a table without unlinking it?
Or, is there a better way to process this issue?

Thanks in advance.
Reply With Quote
  #2  
Old 04-03-2017, 04:15 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,191
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by Kokiri View Post
I have a field pulled from a database that looks roughly like this.

^Total^Fall^Spring
Award 1^$2,000
Award 2-Award^$1,000
Award 3 etc.^$500

I then made a macro that pulls apart this field and formats it as a table without borders. (Minus the underscores)
____________________Total_______Fall_______Spring
Award 1_____________$2,000_____$1000.00___$1000.00
Award 2-Award_______$1,000_____$500.00____$500.00
Award 3 etc._________$500_______$250.00____$250.00
Total_______________$3500.00____$1750.00___$1750.0 0

All of this works great. The problem is, that when the mail merge field is unlinked, it overwrites all records with the first record's data. So all of the letters besides the first will display incorrect information.
Are you saying a single field in the database contains records with:
^Total^Fall^Spring
Award 1^$2,000
Award 2-Award^$1,000
Award 3 etc.^$500
If so, that seem to be a particularly poor database design.

That said, your description suggests you're running the reformatting macro at the wrong point and/or against the wrong document, i.e. running on the mailmerge main document instead of on the merged output (post-merge).
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 04-03-2017, 06:29 PM
Kokiri Kokiri is offline Windows 8 Office 2016
Novice
 
Join Date: Apr 2017
Posts: 4
Kokiri is on a distinguished road
Default

Ha! You're probably right.

I am trying to pull a 1:Many relationship into the letter. In the database, I'm restricted on what I can do with children records. I'm experimenting with other ways that I can get this out of the system (our system has a pretty nice mail merge feature otherwise.) Right now though, I made this field and a similar field that stores information from these child records. The records might have commas and other symbols. That's why I chose ^. I found the convert text to table function and set it up through a macro. After reading a lot of your material, I have a couple other routes that I may explore.

If I ran the macro in the output document, how do I tell the macro what to convert? It appears that all the bookmarks and other references are stripped in the output. These tables can have a variable number of rows, and there are also a variable number of records in the mail merge.
Reply With Quote
  #4  
Old 04-03-2017, 09:28 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,191
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

You should be able to do that using the techniques described in my Microsoft Word Catalogue/Directory Mailmerge Tutorial, available at:
http://windowssecrets.com/forums/sho...merge-Tutorial
or:
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip

Alternatively, you could use a standard letter merge containing a DATABASE field for the tabulation and a macro to drive the process. This, or course, requires you to be familiar with SQL. For a demonstration, see: http://answers.microsoft.com/en-us/o...1-1996c14dca5d
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 04-04-2017, 06:55 AM
Kokiri Kokiri is offline Windows 8 Office 2016
Novice
 
Join Date: Apr 2017
Posts: 4
Kokiri is on a distinguished road
Default

I've looked at the Microsoft Word Catalogue/Directory Mailmerge Tutorial already. I'm not sure that I can go that way, though, because I have two records that I am working out of then. My database only pulls out one at a time, although I can reference other records that have a parental relationship to the record I'm working out of, too. I would have to pull out the person records and then the award records, and then somehow match them back up, right? That's why I store the records in the clumsy field.

Here is the end goal: I have a letter that has fields drawing from the person record in its body. I then have a spot that has a table. This table has the other record, the awards. I don't have many options of pulling them both out at the same time, although I do have the ability to customize the system.
Am I overthinking this?
Reply With Quote
  #6  
Old 04-04-2017, 01:43 PM
Kokiri Kokiri is offline Windows 8 Office 2016
Novice
 
Join Date: Apr 2017
Posts: 4
Kokiri is on a distinguished road
Default

Okay, so I have made some modifications to my process so that the macro runs after clicking "Edit individual Letters." Thanks for pointing that out!

In a Macro, how can I loop through the records on this document? I've looked at your "Sub Merge_To_Individual_Files()", but I can't even get that to work. Is that run from the "Edit Individual Letters" Document?
Reply With Quote
  #7  
Old 04-04-2017, 06:38 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,191
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by Kokiri View Post
I've looked at the Microsoft Word Catalogue/Directory Mailmerge Tutorial already. I'm not sure that I can go that way, though, because I have two records that I am working out of then. My database only pulls out one at a time, although I can reference other records that have a parental relationship to the record I'm working out of, too. I would have to pull out the person records and then the award records, and then somehow match them back up, right?
The tutorial describes how you can group multiple records. Did you experiment with the included data file & field coding? Did you note how there are multiple records for most groups? Did you look at the link I posted re the use of DATABASE fields?

There should be no need for all your circumlocution. The only reason you'd need a macro for the merge is if you're merging to an actual table and you need to delete the intervening paragraphs afterwards.
Quote:
Originally Posted by Kokiri View Post
I've looked at your "Sub Merge_To_Individual_Files()", but I can't even get that to work. Is that run from the "Edit Individual Letters" Document?
That's not even related to the tutorial and won't work with the tutorial's processes.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Tags
macro, mail merge, table

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Losing table alt text when converting to PDF purpledwarf Word 0 01-11-2017 03:23 PM
Mail Merge problem when converting to PDF roflzor Mail Merge 7 09-30-2013 06:50 AM
converting a word document to a data file for mail merge drsuis Mail Merge 4 02-21-2013 03:34 PM
mail merge with table nadja Mail Merge 5 03-06-2012 05:41 PM
Converting data-table into coding text niuri_cigarete Excel 2 12-10-2011 02:57 AM


All times are GMT -7. The time now is 08:21 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft