Shrink to fit long merged name

[Marcia]

Hi. In a document with a merged field of names, is there a command that can shrink the long names to fit in a fixed column width in one line? I do not like the fit text because it distributes/spreads the characters across the width of the column.
Thank you.


*A mother named her baby Hershey Chocolate

[macropod]

You can indeed use the .FitText method in a post-merge macro, but restrict its application to cells in which text wrapping occurs. For example:

        With ActiveDocument.Tables(t).Cell(r, c).Range
            If .Characters.Last.Previous.Information(wdVerticalPositionRelativeToPage) <> _
              .Characters.First.Information(wdVerticalPositionRelativeToPage) Then
              .FitTextWidth = w
            End If
        End With

where:
• t is the table #
• r is the row #
• c is the column#
• w is the width in points

[Marcia]

Thank you Paul.
But, I do not know how to input the row number, column number and column width in the code. The row number is 12, column number 2 and the column width is 2.38 inches. My vba "expertise" is to record repetitive tasks. But editing organic a code stump me.
Marcia

[macropod]

In which case, assuming it's in table 1:

        With ActiveDocument.Tables(1).Cell(12, 2).Range
            If .Characters.Last.Previous.Information(wdVerticalPositionRelativeToPage) <> _
              .Characters.First.Information(wdVerticalPositionRelativeToPage) Then
              .FitTextWidth = InchesToPoints(2.38)
            End If
        End With

[Marcia]

I had to do some google search on how to caption a table.
The code works beautifully on long names. On short names however, the code spreads/distributes the characters on the whole width adding unsightly spaces in between characters.

[macropod]

That could only be happening to your second example if it spanned more than one line. The code specifically tests how many lines the content in each cell spans. Perhaps you have an extraneous paragraph break/line break there? Or are you doing this in the mailmerge main document instead of to the output?

For a generalised mailmerge solution, you might use something like:

Sub MailMergeToDoc()
Application.ScreenUpdating = False
Dim Tbl As Table, Cll As Cell, Par As Paragraph, sCllWdth As Single, sParWdth As Single
With ActiveDocument
  With .Tables(1).Cell(1, 1)
      sCllWdth = .Width - .LeftPadding - .RightPadding
      With .Range.Paragraphs(1)
        sCllWdth = sCllWdth - .LeftIndent - .RightIndent
      End With
    End With
  End With
  .MailMerge.Execute
End With
With ActiveDocument
  For Each Tbl In .Tables
    For Each Cll In Tbl.Range.Cells
      If Len(Cll.Range) > 2 Then
        For Each Par In Cll.Range.Paragraphs
          With Par.Range
            sParWdth = .Characters.Last.Previous.Information(wdHorizontalPositionRelativeToPage) _
              - .Characters.First.Information(wdHorizontalPositionRelativeToPage)
            If sParWdth > sCllWdth Then .FitTextWidth = sCllWdth
            If .Characters.Last.Previous.Information(wdVerticalPositionRelativeToPage) > _
              .Characters.First.Information(wdVerticalPositionRelativeToPage) Then .FitTextWidth = sCllWdth
          End With
        Next
      End If
    Next
  Next
End With
Application.ScreenUpdating = True
End Sub

The above code processes cell content by paragraph, rather than just the whole cell. Not only does this code cater for wrapped lines, it also caters for cells whose content exceeds the cell width where someone has turned line-wrapping off.

The only caveat is that every intended new line in the cells is created by a paragraph break rather than a manual line break.

[Marcia]

My bad. Yes I ran the code in the main document, not the result. But, when I pasted the code to the resulting merged documents of 80 tables, it did not do anything when ran. I tried sending 1 data set only (the long name) instead of the whole 80 datasets and it was good. I selected another data with short name, sent it to editing, ran the code and it worked. It did not spread the characters out.
It is only when there are more than 1 table in the merged document (results) that the code does not do any changes.

[macropod]

The code assumes the table the cell widths are to be calculated from is the first table in the document and, as coded, the first cell in that table. As I said, it's generic. If your mailmerge main document has multiple tables, you'd need to specify which table to use; you'd probably also want to code the macro to skip over other tables.

[Marcia]

Thank you Paul. My workaround is to unselect the few long names, send the short names to the printer then print separately those with long names after running the macro in the main document.