If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using Mail Merge with multiple values
I created a Mail Merge document where I am using Excel to
merge the data into my document. How can I list multiple records on the same document. Below are some Excel records that I am using. My document has a main header, which consist of the Patient's First Name, Last Name, Member#, Acct#, SSN and so on. The data displays on the header just fine. In the middle part of my document, I need to display some of the below listed information. For each patient, I need to list their corresponding DIAG code and DESC on that same form. For example, looking at the below data, Ted Smith's information should be displayed in the middle part of the document as follows. Code Desc 25000 DIABETES 4139 ANGINA 7140 ARTHRITIS After the above information is merged, then the next record for Mike Jones should be displayed on a new page. Is there a way to accomplish this task? My Excel spreadsheet consist of 49,000 rows, so doing something manual will take forever. If anyone knows of a way to help me or put me in the right direction, it will be greatly appreciated. Thanks, Patrick MEMBER# ACCT# LNAME FNAME DOB DIAG DESC 58936057600 17444 SMITH TED 07/03/1935 25000 DIABETES 58936057600 17444 SMITH TED 07/03/1935 4139 ANGINA 58936057600 17444 SMITH TED 07/03/1935 7140 ARTHRITIS 00496426000 12796 JONES MIKE 03/13/1931 4280 HEART FAIL 01242927100 28703 DOE JOHN 08/11/1929 25000 DIABETES 01242927100 28703 DOE JOHN 08/11/1929 41090 MYOCARDIAL |
#2
|
|||
|
|||
Using Mail Merge with multiple values
Hi Patrick,
Take a look at the discussion on doing "one-to-many" types of mail merges in the Special Merges section of the FAQ on my website. There are links to sample files/instructions for the three basic approaches. For simplicity's sake, if you can display the "many" list in a table format, I recommend the DATABASE field approach. My document has a main header, which consist of the Patient's First Name, Last Name, Member#, Acct#, SSN and so on. The data displays on the header just fine. In the middle part of my document, I need to display some of the below listed information. For each patient, I need to list their corresponding DIAG code and DESC on that same form. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
#3
|
|||
|
|||
Using Mail Merge with multiple values
Hi Cindy
I am using merge to fax from Word 2002 and it is working fine. My database has format as below: Add1 Add2 Problem Fax aaa aaaa 11111 [fax:12345678] aaa aaaa 22222 [fax:12345678] bbb bbbb 123488 [fax:23456789] Using standard mail merge there will be 3 calls and 3 pages with problem and its description How can create multiple pages document (first 2 records) and only two calls will be needed to process all recepients list Thanks Zed "Cindy M -WordMVP-" wrote in message news:VA.00009716.014b0df0@speedy... Hi Patrick, Take a look at the discussion on doing "one-to-many" types of mail merges in the Special Merges section of the FAQ on my website. There are links to sample files/instructions for the three basic approaches. For simplicity's sake, if you can display the "many" list in a table format, I recommend the DATABASE field approach. My document has a main header, which consist of the Patient's First Name, Last Name, Member#, Acct#, SSN and so on. The data displays on the header just fine. In the middle part of my document, I need to display some of the below listed information. For each patient, I need to list their corresponding DIAG code and DESC on that same form. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
#4
|
|||
|
|||
Using Mail Merge with multiple values
Hi Zed,
You may need to modify this to do exactly what you want: ' Macro to create multiple items per condition from a document created by a catalog or directory type mailmerge Dim source As Document, target As Document, scat As Range, tcat As Range Dim data As Range, stab As Table, ttab As Table Dim i As Long, j As Long, k As Long, n As Long Set source = ActiveDocument Set target = Documents.Add Set stab = source.Tables(1) k = stab.Columns.Count Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1, numcolumns:=k - 1) Set scat = stab.Cell(1, 1).Range scat.End = scat.End - 1 ttab.Cell(1, 1).Range = scat j = ttab.Rows.Count For i = 1 To stab.Rows.Count Set tcat = ttab.Cell(j, 1).Range tcat.End = tcat.End - 1 Set scat = stab.Cell(i, 1).Range scat.End = scat.End - 1 If scat tcat Then ttab.Rows.Add j = ttab.Rows.Count ttab.Cell(j, 1).Range = scat ttab.Rows.Add For n = 2 To k Set data = stab.Cell(i, n).Range data.End = data.End - 1 ttab.Cell(ttab.Rows.Count, n - 1).Range = data Next n Else ttab.Rows.Add For n = 2 To k Set data = stab.Cell(i, n).Range data.End = data.End - 1 ttab.Cell(ttab.Rows.Count, n - 1).Range = data Next n End If Next i -- Please post any further questions or followup to the newsgroups for the benefit of others who may be interested. Unsolicited questions forwarded directly to me will only be answered on a paid consulting basis. Hope this helps Doug Robbins - Word MVP "Zed Gorski" wrote in message m.au... Hi Cindy I am using merge to fax from Word 2002 and it is working fine. My database has format as below: Add1 Add2 Problem Fax aaa aaaa 11111 [fax:12345678] aaa aaaa 22222 [fax:12345678] bbb bbbb 123488 [fax:23456789] Using standard mail merge there will be 3 calls and 3 pages with problem and its description How can create multiple pages document (first 2 records) and only two calls will be needed to process all recepients list Thanks Zed "Cindy M -WordMVP-" wrote in message news:VA.00009716.014b0df0@speedy... Hi Patrick, Take a look at the discussion on doing "one-to-many" types of mail merges in the Special Merges section of the FAQ on my website. There are links to sample files/instructions for the three basic approaches. For simplicity's sake, if you can display the "many" list in a table format, I recommend the DATABASE field approach. My document has a main header, which consist of the Patient's First Name, Last Name, Member#, Acct#, SSN and so on. The data displays on the header just fine. In the middle part of my document, I need to display some of the below listed information. For each patient, I need to list their corresponding DIAG code and DESC on that same form. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
#5
|
|||
|
|||
Using Mail Merge with multiple values
Hi,
Thanks for response I am interested how to solve my problem on conceptual level First question: Can be done without VBA If VBA has to be used , please provide only step by step procedure Thanks Zed "Doug Robbins - Word MVP" wrote in message ... Hi Zed, You may need to modify this to do exactly what you want: ' Macro to create multiple items per condition from a document created by a catalog or directory type mailmerge Dim source As Document, target As Document, scat As Range, tcat As Range Dim data As Range, stab As Table, ttab As Table Dim i As Long, j As Long, k As Long, n As Long Set source = ActiveDocument Set target = Documents.Add Set stab = source.Tables(1) k = stab.Columns.Count Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1, numcolumns:=k - 1) Set scat = stab.Cell(1, 1).Range scat.End = scat.End - 1 ttab.Cell(1, 1).Range = scat j = ttab.Rows.Count For i = 1 To stab.Rows.Count Set tcat = ttab.Cell(j, 1).Range tcat.End = tcat.End - 1 Set scat = stab.Cell(i, 1).Range scat.End = scat.End - 1 If scat tcat Then ttab.Rows.Add j = ttab.Rows.Count ttab.Cell(j, 1).Range = scat ttab.Rows.Add For n = 2 To k Set data = stab.Cell(i, n).Range data.End = data.End - 1 ttab.Cell(ttab.Rows.Count, n - 1).Range = data Next n Else ttab.Rows.Add For n = 2 To k Set data = stab.Cell(i, n).Range data.End = data.End - 1 ttab.Cell(ttab.Rows.Count, n - 1).Range = data Next n End If Next i -- Please post any further questions or followup to the newsgroups for the benefit of others who may be interested. Unsolicited questions forwarded directly to me will only be answered on a paid consulting basis. Hope this helps Doug Robbins - Word MVP "Zed Gorski" wrote in message m.au... Hi Cindy I am using merge to fax from Word 2002 and it is working fine. My database has format as below: Add1 Add2 Problem Fax aaa aaaa 11111 [fax:12345678] aaa aaaa 22222 [fax:12345678] bbb bbbb 123488 [fax:23456789] Using standard mail merge there will be 3 calls and 3 pages with problem and its description How can create multiple pages document (first 2 records) and only two calls will be needed to process all recepients list Thanks Zed "Cindy M -WordMVP-" wrote in message news:VA.00009716.014b0df0@speedy... Hi Patrick, Take a look at the discussion on doing "one-to-many" types of mail merges in the Special Merges section of the FAQ on my website. There are links to sample files/instructions for the three basic approaches. For simplicity's sake, if you can display the "many" list in a table format, I recommend the DATABASE field approach. My document has a main header, which consist of the Patient's First Name, Last Name, Member#, Acct#, SSN and so on. The data displays on the header just fine. In the middle part of my document, I need to display some of the below listed information. For each patient, I need to list their corresponding DIAG code and DESC on that same form. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
#6
|
|||
|
|||
Using Mail Merge with multiple values
Hi Zed,
I am interested how to solve my problem on conceptual level First question: Can be done without VBA If VBA has to be used , please provide only step by step procedure Did you read up on the one-to-many information on my website, in the Special Merges section? I can assure you, we have no interest in re-typing that very basic information. That's why it's on the website :-) Follow-up questions to the various approaches listed there, yes. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
#7
|
|||
|
|||
Using Mail Merge with multiple values
Hi Cindy,
Thank you for sugestions and response Zed "Cindy M -WordMVP-" wrote in message news:VA.0000977d.00a2ca02@speedy... Hi Zed, I am interested how to solve my problem on conceptual level First question: Can be done without VBA If VBA has to be used , please provide only step by step procedure Did you read up on the one-to-many information on my website, in the Special Merges section? I can assure you, we have no interest in re-typing that very basic information. That's why it's on the website :-) Follow-up questions to the various approaches listed there, yes. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
Thread Tools | |
Display Modes | |
|
|