Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-12-2022, 09:15 PM
Marcia's Avatar
Marcia Marcia is offline Shrink to fit long merged name Windows 11 Shrink to fit long merged name Office 2021
Expert
Shrink to fit long merged name
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Shrink to fit long merged name


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
Reply With Quote
  #2  
Old 09-13-2022, 04:23 PM
macropod's Avatar
macropod macropod is offline Shrink to fit long merged name Windows 10 Shrink to fit long merged name Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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 can indeed use the .FitText method in a post-merge macro, but restrict its application to cells in which text wrapping occurs. For example:
Code:
        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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 09-14-2022, 05:48 AM
Marcia's Avatar
Marcia Marcia is offline Shrink to fit long merged name Windows 11 Shrink to fit long merged name Office 2021
Expert
Shrink to fit long merged name
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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
Reply With Quote
  #4  
Old 09-14-2022, 05:59 AM
macropod's Avatar
macropod macropod is offline Shrink to fit long merged name Windows 10 Shrink to fit long merged name Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

In which case, assuming it's in table 1:
Code:
        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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 09-14-2022, 08:32 PM
Marcia's Avatar
Marcia Marcia is offline Shrink to fit long merged name Windows 11 Shrink to fit long merged name Office 2021
Expert
Shrink to fit long merged name
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Attached Images
File Type: png FitText.png (4.9 KB, 6 views)
Reply With Quote
  #6  
Old 09-15-2022, 02:27 PM
macropod's Avatar
macropod macropod is offline Shrink to fit long merged name Windows 10 Shrink to fit long merged name Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

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:
Code:
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 09-15-2022, 07:16 PM
Marcia's Avatar
Marcia Marcia is offline Shrink to fit long merged name Windows 11 Shrink to fit long merged name Office 2021
Expert
Shrink to fit long merged name
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #8  
Old 09-17-2022, 06:40 AM
macropod's Avatar
macropod macropod is offline Shrink to fit long merged name Windows 10 Shrink to fit long merged name Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 09-20-2022, 06:03 AM
Marcia's Avatar
Marcia Marcia is offline Shrink to fit long merged name Windows 11 Shrink to fit long merged name Office 2021
Expert
Shrink to fit long merged name
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Shrink to fit long merged name How to unmerge all merged cell and fill them with the merged values? Bumba Excel Programming 1 11-10-2019 11:36 AM
Shrink to fit long merged name Why my Docm file does not shrink in size? eduzs Word VBA 3 09-16-2018 04:13 AM
Shrink to fit long merged name Does Word automatically shrink graphics? jrasicmark Drawing and Graphics 1 05-06-2014 04:38 PM
Custom Animation: Faded Shrink? Dingeling PowerPoint 2 12-14-2011 02:14 AM
Possible to shrink .DOC file? uoficowboy Word 0 06-09-2010 10:13 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:18 PM.


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