#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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. |
#4
|
||||
|
||||
You should be able to do that using the techniques described in my Microsoft Word Catalogue/Directory Mailmerge Tutorial:
https://www.msofficeforums.com/mail-...-tutorial.html 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: Many to one email merge using tables - Microsoft Community
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
#7
|
||||
|
||||
Quote:
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. That's not even related to the tutorial and won't work with the tutorial's processes.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
macro, mail merge, table |
|
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 |