Mailmerge problem using Database field

Tesla · #1 04-04-2022, 08:26 AM

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

Charles Kenyon · #2 04-04-2022, 10:26 AM

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}

(screenshot)
00 deleteme 9.png

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/

macropod · #3 04-04-2022, 04:20 PM

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}

Tesla · #4 04-05-2022, 04:29 AM

Hello,
Any support on how I can add to my table, a last row which contain the totals. Attached is my sample table.
Regards

macropod · #5 04-05-2022, 02:49 PM

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?

Tesla · #6 04-06-2022, 12:41 AM

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.

Tesla · #7 04-08-2022, 07:38 AM

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

macropod · #8 04-08-2022, 07:28 PM

Quote:

Originally Posted by Tesla

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?

It seems you haven't even attempted to adapt the code from your other thread. The code required for this project is way simpler:

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

And, if you don't want to retain the £ characters being output by the DATABASE field, surely the appropriate way to do that is to not have the DATABASE field insert them in the first place.

Tesla · #9 04-09-2022, 12:33 PM

The code works, thank you

Tesla · #10 05-05-2022, 02:09 AM

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

macropod · #11 05-05-2022, 06:54 AM

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

Tesla · #12 05-05-2022, 07:17 AM

Hello,
The code works, I really appreciate.

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