Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-04-2022, 08:26 AM
Tesla Tesla is offline Mailmerge problem using Database field Windows 7 32bit Mailmerge problem using Database field Office 2007
Advanced Beginner
Mailmerge problem using Database field
 
Join Date: Sep 2018
Posts: 59
Tesla is on a distinguished road
Default 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
Attached Files
File Type: docx letter.docx (21.0 KB, 8 views)
File Type: xls list.xls (53.0 KB, 8 views)
Reply With Quote
  #2  
Old 04-04-2022, 10:26 AM
Charles Kenyon Charles Kenyon is online now Mailmerge problem using Database field Windows 10 Mailmerge problem using Database field Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 7,976
Charles Kenyon has much to be proud ofCharles Kenyon has much to be proud ofCharles Kenyon has much to be proud ofCharles Kenyon has much to be proud ofCharles Kenyon has much to be proud ofCharles Kenyon has much to be proud ofCharles Kenyon has much to be proud ofCharles Kenyon has much to be proud ofCharles Kenyon has much to be proud ofCharles Kenyon has much to be proud of
Default

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/
Reply With Quote
  #3  
Old 04-04-2022, 04:20 PM
macropod's Avatar
macropod macropod is offline Mailmerge problem using Database field Windows 10 Mailmerge problem using Database field Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,450
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #4  
Old 04-05-2022, 04:29 AM
Tesla Tesla is offline Mailmerge problem using Database field Windows 7 32bit Mailmerge problem using Database field Office 2007
Advanced Beginner
Mailmerge problem using Database field
 
Join Date: Sep 2018
Posts: 59
Tesla is on a distinguished road
Default How to make totals in a table from a mail merge

Hello,
Any support on how I can add to my table, a last row which contain the totals. Attached is my sample table.
Regards
Attached Files
File Type: docx letter.docx (14.4 KB, 2 views)
File Type: xls list.xls (46.5 KB, 1 views)
Reply With Quote
  #5  
Old 04-05-2022, 02:49 PM
macropod's Avatar
macropod macropod is offline Mailmerge problem using Database field Windows 10 Mailmerge problem using Database field Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,450
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #6  
Old 04-06-2022, 12:41 AM
Tesla Tesla is offline Mailmerge problem using Database field Windows 7 32bit Mailmerge problem using Database field Office 2007
Advanced Beginner
Mailmerge problem using Database field
 
Join Date: Sep 2018
Posts: 59
Tesla is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 04-08-2022, 07:38 AM
Tesla Tesla is offline Mailmerge problem using Database field Windows 7 32bit Mailmerge problem using Database field Office 2007
Advanced Beginner
Mailmerge problem using Database field
 
Join Date: Sep 2018
Posts: 59
Tesla is on a distinguished road
Default 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
Attached Files
File Type: docx letter.docx (21.5 KB, 10 views)
File Type: xls list.xls (40.5 KB, 6 views)

Last edited by macropod; 04-08-2022 at 07:24 PM. Reason: Repaired code formatting
Reply With Quote
  #8  
Old 04-08-2022, 07:28 PM
macropod's Avatar
macropod macropod is offline Mailmerge problem using Database field Windows 10 Mailmerge problem using Database field Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,450
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Tesla View Post
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 04-09-2022, 12:33 PM
Tesla Tesla is offline Mailmerge problem using Database field Windows 7 32bit Mailmerge problem using Database field Office 2007
Advanced Beginner
Mailmerge problem using Database field
 
Join Date: Sep 2018
Posts: 59
Tesla is on a distinguished road
Default

The code works, thank you
Reply With Quote
  #10  
Old 05-05-2022, 02:09 AM
Tesla Tesla is offline Mailmerge problem using Database field Windows 7 32bit Mailmerge problem using Database field Office 2007
Advanced Beginner
Mailmerge problem using Database field
 
Join Date: Sep 2018
Posts: 59
Tesla is on a distinguished road
Default 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
Reply With Quote
  #11  
Old 05-05-2022, 06:54 AM
macropod's Avatar
macropod macropod is offline Mailmerge problem using Database field Windows 10 Mailmerge problem using Database field Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,450
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #12  
Old 05-05-2022, 07:17 AM
Tesla Tesla is offline Mailmerge problem using Database field Windows 7 32bit Mailmerge problem using Database field Office 2007
Advanced Beginner
Mailmerge problem using Database field
 
Join Date: Sep 2018
Posts: 59
Tesla is on a distinguished road
Default

Hello,
The code works, I really appreciate.
Reply With Quote
Reply

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
Mailmerge problem using Database field 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
Mailmerge problem using Database field 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
Mailmerge problem using Database field insert database as field david_89_ Mail Merge 3 03-26-2014 06:02 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:55 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft