Intermittent Text Wrapping in Tables Copied from Excel

[svendfj]

I have working code that copies a series of tables from Excel into Word (Office 2016). The first row and certain other rows in each table has text only in the first column. These rows serve as a heading row for that set of rows. A row with text only in the first column identifies the beginning of a new set of rows. When copying into Word, these rows identify the beginning of a new "Area". For these rows, I do not want the text in that first cell to wrap - I want that text to spill over into as many additional cells as needed to display in a straight line. In Excel, 'Wrap Text' is turned off for these rows, and the text in all of these rows displays perfectly - in Excel.

However, after copying into Word, the text wraps automatically, on its own, for about 1 in 5 of these rows. To try to eliminate this random text wrapping, aside from having 'Wrap Text' turned off, I have also tried merging all cells in these rows, making certain that there are no hidden or other characters in the empty cells in these rows. However no matter what I try, the problem persists: seemingly at random, after copying into Word, some text in rows with text only in the first column will wrap on its own, instead of filling the rest of that row and displaying in a straight line. There is no apparent connection between when the text wraps and the length of the text. When the text wraps, sometimes it wraps in the first column, sometimes it wraps after the second column, and sometimes after filling the first three columns, the wrapping starts.

If after copying into Word, I force the text in these rows (rows with text in only the first column) to not wrap (.Range.Cells(1).WordWrap = False), then I have do some intensive formatting for each and every column in each row, while switching back and forth between the 'heading rows' (text only in first column) and the other rows. Before doing this, I am trying to identify a more simple solution. I much prefer the simplicity of using Excel to create the desired formatting. This allows for flexibility in developing different content and types of reports in Excel, before copying into Word.

Thank you in advance for any suggestions to resolve this.

Sven

 

[GWteB]

A picture in Word could be a good alternative for a real table. If you can live with that, the code below may help you.

Public Sub Example()

    Dim wdApp   As Object
    Dim oDoc    As Object
    Dim oPic    As Picture

    With ActiveSheet                        ' << change to suit
        .ListObjects("Table1").Range.Copy   ' << change to suit
        Set oPic = .Pictures.Paste(Link:=False)
    End With
    Application.CutCopyMode = False

    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    Set oDoc = wdApp.Documents.Add

    oPic.Cut
    Call PastePicture(oDoc)
End Sub

Public Sub PastePicture(ByVal argWordDoc As Object)
    Dim ils     As Object
    Dim shp     As Object
    Dim lNrIls  As Long
    Dim rngDoc  As Object
    Dim rngSel  As Object

    Set rngDoc = argWordDoc.Content
    Set rngSel = argWordDoc.Parent.Selection.Range
    rngDoc.End = rngSel.End + 1

    lNrIls = rngDoc.InlineShapes.Count
    rngSel.Paste
    Set ils = rngDoc.InlineShapes(lNrIls + 1)
    Set shp = ils.ConvertToShape
    shp.WrapFormat.Type = 5     'wdWrapBehind
End Sub

 

[svendfj]

Thank you very much GWteB!. I appreciate it. Unfortunately, I do need to have actual text brought into MS Word. I have discovered the issue apparently is related to a temporary column that I was using in Excel, that I deleted well before the process of copying the tables into Word. Somehow the temporary column disrupts the continuity of the worksheet, even after it is removed(!) As a workaround, I have found that if I pad the end of all of the strings in these rows with a large number of spaces, the issue goes away.

 

[GWteB]

You are welcome and thanks for letting me know.