#1
|
|||
|
|||
Mailmerge problem using Database field
Hello, Any assistance of how to correct the error occurred while trying to make a mailmerge of one recipient receiving data in multiple rows of excel. Please find attached the documents I am using (word document and an excel sheet containing data) Thank you |
#2
|
|||
|
|||
This is your field.
{DATABASE \d "{FILENAME \p}/../list.xlsx" \s " SELECT Format([Period], 'MMM-YYYY') AS `Period`, [Type] AS `Type`, Format([claimed],'£#,##0') AS `claimed`, Format([Paid],'£#,##0') AS `Paid`, Format([fine 50%],'£#,##0') AS `fine 50%`, Format([Penalties 10%],'£#,##0') AS `Penalties 10%`, Format([Fixed fine],'£#,##0') AS `Fixed fine`, Format([Penalties pid],'£#,##0') AS `Penalties pid` , Format([Total to be paid],'£#,##0') AS `Total claim` , Format([Interests],' DD-MMM-YY ') AS `Interests` FROM [Invoices$] WHERE [name] = '{ MERGEFIELD name }' ORDER BY [Period]" \l "23" \b "1087" \h} I have no problem connecting the source file but it does not interpret in your field. Someone here with more experience with using the DATABASE field may be able to help. I am moving your question to the Mail Merge Forum. https://www.msofficeforums.com/mail-merge/ |
#3
|
||||
|
||||
You have a simple mis-match between a field name in the source and the field name in the DATABASE field (i.e. Format([Total to be paid],'£#,##0') AS `Total claim` should be Format([Total claim],'£#,##0') AS `Total claim` or as Format([Total claim],'£#,##0') AS `Total to be paid`).
Your field coding could also be simplified somewhat: {DATABASE \d "{FILENAME \p}/../list.xls" \s " SELECT Format([Period], 'MMM-YYYY') AS `Period`, [Type], Format([claimed],'£#,##0') AS Claimed, Format([Paid],'£#,##0') AS Paid, Format([fine 50%],'£#,##0') AS `Fine 50%`, Format([Penalties 10%],'£#,##0') AS `Penalties 10%`, Format([Fixed fine],'£#,##0') AS `Fixed fine`, Format([Penalties pid],'£#,##0') AS `Penalties pid`, Format([Total claim],'£#,##0') AS `Total claim`, Format([Interests],' DD-MMM-YY ') AS Interests FROM [Invoices$] WHERE [name] = '{MERGEFIELD name}' ORDER BY [Period]" \l "23" \b "1087" \h}
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
How To make totals on a table made by mailmerge
Hello,
Any support on how I can add to my table, a last row which contain the totals. Attached is my sample table. Regards |
#5
|
||||
|
||||
We have been through this before... (https://www.msofficeforums.com/mail-...tml#post151706)
You could adapt that code to this situation. What have you tried?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
Thank you, it works on the document you worked on. To adapt to the document I have now (which is slightly different), it is difficult, because I don't know Visual basic coding. The solution is to learn that, which I will do.
|
#7
|
|||
|
|||
How to modify the VBA code below to add totals and remove "£"
Hello,
Any help to modify the VBA code below so that it can add totals for each columns and also to remove "£" on attached mail merge document? Thank you 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 Application.ScreenUpdating = True End Sub Last edited by macropod; 04-08-2022 at 07:24 PM. Reason: Repaired code formatting |
#8
|
||||
|
||||
Quote:
Code:
Sub MailMergeToDoc() Application.ScreenUpdating = False Dim Rng As Range, t As Long, r As Long, c As Long ActiveDocument.MailMerge.Execute With ActiveDocument.Range For t = 1 To .Tables.Count With .Tables(t) .AllowAutoFit = False .Range.ParagraphFormat.Alignment = wdAlignParagraphRight .Rows.Alignment = wdAlignRowCenter .Rows(1).HeadingFormat = True .Rows(1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter .Columns.PreferredWidthType = wdPreferredWidthPoints .Columns.PreferredWidth = InchesToPoints(0.625) .Columns(1).PreferredWidth = InchesToPoints(0.75) .Columns(10).PreferredWidth = InchesToPoints(0.75) .Rows.Add: r = .Rows.Count .Cell(r, 1).Range.Text = "TOTAL:" .Rows(r).Range.Font.Bold = True For c = 3 To 9 Set Rng = .Cell(r, c).Range Rng.Collapse wdCollapseStart Rng.Fields.Add Rng, wdFieldEmpty, "=SUM(ABOVE) \# £,#0", False Next End With Next .Fields.Unlink End With Set Rng = Nothing Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
The code works, thank you
|
#10
|
|||
|
|||
How to separate tables
Hello,
Thank you for the previous support. If possible can be there any support on how to separate tables with respect to years. The data to use is attached Feb-17 Feb-17 Feb-18 Feb-18 Feb-17 Mar-17 Thank you |
#11
|
||||
|
||||
For example:
Code:
Sub MailMergeToDoc() Application.ScreenUpdating = False Dim Rng As Range, t As Long, r As Long, c As Long, x As Long, y As Long ActiveDocument.MailMerge.Execute With ActiveDocument.Range For t = 1 To .Tables.Count With .Tables(t) .AllowAutoFit = False .Range.ParagraphFormat.Alignment = wdAlignParagraphRight .Rows.Alignment = wdAlignRowCenter .Rows(1).HeadingFormat = True .Rows(1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter .Columns.PreferredWidthType = wdPreferredWidthPoints .Columns.PreferredWidth = InchesToPoints(0.625) .Columns(1).PreferredWidth = InchesToPoints(0.75) .Columns(10).PreferredWidth = InchesToPoints(0.75) Set Rng = .Rows(1).Range: r = .Rows.Count x = Split(Split(.Cell(r, 1).Range.Text, vbCr)(0), "-")(1) For r = .Rows.Count - 1 To 2 Step -1 y = Split(Split(.Cell(r, 1).Range.Text, vbCr)(0), "-")(1) If x <> y Then .Split .Rows(r + 1): x = y With .Range.Characters.Last.Next .Collapse wdCollapseEnd .FormattedText = Rng.FormattedText End With End If Next End With Next Exit Sub For t = 1 To .Tables.Count With .Tables(t) .Rows.Add: r = .Rows.Count .Cell(r, 1).Range.Text = "TOTAL:" .Rows(r).Range.Font.Bold = True For c = 3 To 9 Set Rng = .Cell(r, c).Range Rng.Collapse wdCollapseStart Rng.Fields.Add Rng, wdFieldEmpty, "=SUM(ABOVE) \# £,#0", False Next End With Next .Fields.Unlink End With Set Rng = Nothing Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
|||
|
|||
Hello,
The code works, I really appreciate. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Ref field to Database field doesn't update properly in Mailmerge | huynguyen865 | Mail Merge | 1 | 08-30-2021 07:30 AM |
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 |
database field table lines | Sarki76 | Mail Merge | 3 | 06-26-2019 03:13 PM |
Database field cell alignment | scubadunc | Mail Merge | 9 | 08-12-2014 11:02 PM |
insert database as field | david_89_ | Mail Merge | 3 | 03-26-2014 06:02 AM |