#16
|
|||
|
|||
Look into using styles for your formatting. These can also be created and applied using code and will make your editing in the future much easier.
Importance of Styles in Word |
#17
|
|||
|
|||
Hello, thank you again for the help, but I still have one difficulty. Some debtors have may invoices which cover two or three pages. The code i have enables me to list those invoices but concluding information below the list is not seen. I wish I could generate a letter which lists invoices and the message below the invoices. I attach an example of invoices I have and the code to generate the letter.
Thank you for helping me to make a code that will enable me to produce a letter of one or more pages containing the list of invoices and concluding information. |
#18
|
||||
|
||||
The number of pages a particular group might span is of no consequence; anything that doesn't fit on the first page will appear on the second or later page, as appropriate.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#19
|
|||
|
|||
Thank you macropod, may be I explained less clearly my problem. If I can summarize what I want is this:
Introductory comment Invoice 1 Invoice 2 Invoice 3 page break Invoice 4 Invoice 5 Invoice 6 page break Invoice 7 Invoice 8 Invoice 9 concluding comment The problem I have is that "concluding comment" desapears when invoices are on more than one pages. Any help is welcome |
#20
|
||||
|
||||
That problem has nothing to do with the number of rows in a group but everything to do with your failure to include the dummy last row specified repeatedly in the tutorial.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#21
|
|||
|
|||
Thank you very much, you helped me.
|
#22
|
|||
|
|||
Hello again, I am returning after you helped me to find my first solution. I think important to post my new problem here because it is a continuation of previous conversations.
The first solution enabled me to produce a letter having an introduction, a body composed of a list of invoices and a conclusion. My new problem is to find a solution to include more than one list in the body of the letter like this: Introduction Body Table 1 Table 2 Table 3 Conlusion I am a novice in this subject of mail merge. Any help is welcome |
#23
|
||||
|
||||
What determines the content of these other tables?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#24
|
|||
|
|||
Table 1 is a list of invoices issues to the debtor. Table 2 is a list of delivery notes of goods my company purchased from that debtor. In fact we buy and sell to each other. I want to inform the debtor (creditor also) the list of invoices my company issued to them and a list of delivery notes we received confirming goods we purchased from that debtor. Between the two tables there is a small comment. This is how it should look like (I attach sample invoices and delivery notes. In green color are invoices, in blue color are delivery notes):
Introduction Body These are invoices we issued to you Table 1(list of invoices) These are delivery notes we received from you Table 2 (list of delivery notes) Conlusion |
#25
|
||||
|
||||
You can't use a workbook with such a data layout for the kind of merge you want to do.
You could, however, use a structure such as that in the attached workbook with a mailmerge main document like the one also attached - note the completely different approach to the mailmerge in that document, which uses two DATABASE fields to group the data. As the DATABASE field code is written, it is assumed the mailmerge main document will be stored in the same folder as the Excel workbook. Simply connect the 'Letter' document to the 'List' workbook for an ordinary 'letter' merge, using the 'Clients' worksheet as the data source, then complete the merge. There's a macro in the 'Letter' document that intercepts the Finish & Merge>Edit Individual documents process to do some post-merge table formatting.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#26
|
|||
|
|||
Thank you very much macropod for your time to write the code and clear explanations, the code works without problem. I wish I could be able to help others as well as a recognition of your help to me. If possible could you please show me how to add subtotals for each month and general total?.
Thank you |
#27
|
||||
|
||||
Quote:
Try replacing the existing macro with: Code:
Sub MailMergeToDoc() Application.ScreenUpdating = False Dim s As Long, c As Long, r As Long, t As Long Dim Tbl As Table, StrRDt As String, StrCDt As String, Rng As Range Dim u As Long, v As Long, w As Long, x As Long ActiveDocument.MailMerge.Execute With ActiveDocument For s = 1 To .Sections.Count ' - 1 Set Tbl = .Sections(s).Range.Tables(1) With Tbl .Range.ParagraphFormat.Alignment = wdAlignParagraphRight .Rows.Alignment = wdAlignRowCenter .Rows(1).HeadingFormat = True .Rows(1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter For c = 3 To 5 .Columns(c).PreferredWidthType = wdPreferredWidthPoints .Columns(c).PreferredWidth = InchesToPoints(1) Next StrRDt = Split(.Cell(.Rows.Count, 1).Range.Text, vbCr)(0) StrRDt = Split(StrRDt, "-")(1) & "-" & Split(StrRDt, "-")(2) .Rows.Add: t = .Rows.Count For r = t - 1 To 2 Step -1 StrCDt = Split(.Cell(r, 1).Range.Text, vbCr)(0) StrCDt = Split(StrCDt, "-")(1) & "-" & Split(StrCDt, "-")(2) If StrRDt = StrCDt Then For c = 3 To 5 Select Case c Case 3: u = u + Split(Split(.Cell(r, c).Range.Text, vbCr)(0), "£")(1) Case 4: v = v + Split(Split(.Cell(r, c).Range.Text, vbCr)(0), "£")(1) Case 5: w = w + Split(Split(.Cell(r, c).Range.Text, vbCr)(0), "£")(1) End Select Next ElseIf (StrRDt <> StrCDt) Or (r = 2) Then .Cell(t, 1).Range.Text = StrRDt .Rows(t).Range.Font.Italic = True For c = 3 To 5 Select Case c Case 3 .Cell(t, c).Range.Text = Format(u, "£#,##0") u = Split(Split(.Cell(r, c).Range.Text, vbCr)(0), "£")(1) Case 4 .Cell(t, c).Range.Text = Format(v, "£#,##0") v = Split(Split(.Cell(r, c).Range.Text, vbCr)(0), "£")(1) Case 5 .Cell(t, c).Range.Text = Format(w, "£#,##0") w = Split(Split(.Cell(r, c).Range.Text, vbCr)(0), "£")(1) End Select Next StrRDt = StrCDt: t = r + 1 If r <> 2 Then .Rows.Add Tbl.Rows(r + 1) Else Exit For End If End If Next .Cell(t, 1).Range.Text = StrRDt For c = 3 To 5 Select Case c Case 3 .Cell(t, c).Range.Text = Format(u, "£#,##0") Case 4 .Cell(t, c).Range.Text = Format(v, "£#,##0") Case 5 .Cell(t, c).Range.Text = Format(w, "£#,##0") End Select Next .Rows(t).Range.Font.Italic = True .Rows.Add: t = .Rows.Count For c = 3 To 5 Set Rng = .Cell(t, c).Range Rng.Collapse wdCollapseStart Rng.Fields.Add Rng, wdFieldEmpty, "=SUM(ABOVE)/2 \# £,#0", False Next .Cell(t, 1).Range.Text = "TOTAL:" .Rows(t).Range.Font.Bold = True End With Set Tbl = .Sections(s).Range.Tables(2) With Tbl .Range.ParagraphFormat.Alignment = wdAlignParagraphRight .Rows.Alignment = wdAlignRowCenter .Rows(1).HeadingFormat = True .Rows(1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter StrRDt = Split(.Cell(.Rows.Count, 1).Range.Text, vbCr)(0) StrRDt = Split(StrRDt, "-")(1) & "-" & Split(StrRDt, "-")(2) .Rows.Add: t = .Rows.Count For r = t - 1 To 2 Step -1 StrCDt = Split(.Cell(r, 1).Range.Text, vbCr)(0) StrCDt = Split(StrCDt, "-")(1) & "-" & Split(StrCDt, "-")(2) If StrRDt = StrCDt Then x = x + Split(Split(.Cell(r, 2).Range.Text, vbCr)(0), "£")(1) ElseIf (StrRDt <> StrCDt) Or (r = 2) Then .Cell(t, 1).Range.Text = StrRDt .Rows(t).Range.Font.Italic = True .Cell(t, 2).Range.Text = Format(x, "£#,##0") StrRDt = StrCDt: t = r + 1 If r <> 2 Then .Rows.Add Tbl.Rows(r + 1) Else Exit For End If End If Next .Cell(t, 1).Range.Text = StrRDt .Cell(t, 2).Range.Text = Format(x, "£#,##0") .Rows(t).Range.Font.Italic = True .Rows.Add: t = .Rows.Count Set Rng = .Cell(t, 2).Range Rng.Collapse wdCollapseStart Rng.Fields.Add Rng, wdFieldEmpty, "=SUM(ABOVE)/2 \# £,#0", False .Cell(t, 1).Range.Text = "TOTAL:" .Rows(t).Range.Font.Bold = True End With Next .Fields.Unlink End With Set Rng = Nothing: Set Tbl = Nothing Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#28
|
|||
|
|||
Hello again, I am really sorry I am a beginner,
Trying to replace existing macro was also a problem, I don't know how to do it. I went on View-Macros-Edit. When I printed the document, it came as if I did no thing. Previously I thought I would do subtotals myself on the basis of the tutorial "Using Mailmerge To Create Category-Based Lists", I didn't want to give you the work I can learn to do myself, but the code you developed was different from the code included in the tutorial, so I couldn't know where to insert the code for subtotals and the general total. This is why I didn't express my request previously. |
#29
|
||||
|
||||
Quote:
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#30
|
|||
|
|||
Quote:
I did as instructed above but I don't see "MailMerge ToDoc macro" so that I can replace it. I attach a sreen shot of my computer |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro to insert an image to a word mailmerge document based on the value of a mailmerge field? | Jake93 | Mail Merge | 3 | 07-02-2019 05:38 PM |
Use L and R arrow to move cursor letter to letter, not top of the line | mellowkitten | Word | 2 | 05-12-2016 07:23 PM |
How to replace a letter to random letter with different color? | cikanoz87 | Word | 7 | 06-18-2015 09:43 PM |
Mailmerge with grouped data | Keith Henderson | Mail Merge | 14 | 02-04-2015 01:45 PM |
Directory MailMerge with Category Grouped Lists on Labels | screech | Mail Merge | 3 | 06-18-2014 11:41 PM |