CODEWITHSUNDEEP
excelvbams-word
Gexas
Gexas

Reputation: 678

Paste table from excel to Word then Fit Window and distribute columns evenly

I am trying to copy and paste table from excel to word, but since table is bigger than sheet it goes out of page bounds.

I found out that .AutoFitBehavior (wdAutoFitWindow) helps with that, but it distributes column width based on information in the cells. Because of that some columns are very thin and vice versa. I would prefer them to be distributed evenly and only row size vary.

To my knowledge .Tables(3).Columns.DistributeWidth should help with even distribution.

However I cannot manage to combine these two lines. Depending on code variation I tried either one of them works, but never both.

My current relevant code part:

tbl.copy

Set objDoc = objWord.Documents.Add(Template:="whatever", NewTemplate:=False, DocumentType:=0)
With objDoc
    .Range.Bookmarks("lentele").Range.PasteExcelTable _
         LinkedToExcel:=False, _
         WordFormatting:=False, _
         RTF:=True
End With

Set WordTable = objDoc.Tables(3)
WordTable.AutoFitBehavior (wdAutoFitWindow)

With objDoc
    .Tables(3).Columns.DistributeWidth
End With

Additional question: it would be great if all text in inserted table would be decreased to font size 8, but I get error then I enter this line objDoc.Tables(3).Font.Size = 8

Upvotes: 0

Views: 1804

Answers (1)

macropod
macropod

Reputation: 13505

If you insert a suitably-formatted (no auto-width) table into the Word template beforehand with just the first row of the table (e.g. a heading row, or even an empty row), you could use the PasteAppendTable method, which will force the appended table to adopt the same column widths. If you need to use a dummy row for that, it could be deleted afterwards.

Nevertheless, whilst retaining your current approach:

Set objDoc = objWord.Documents.Add(Template:="whatever", NewTemplate:=False, DocumentType:=0)
tbl.Copy
With objDoc.Range.Bookmarks("lentele").Range
    .PasteExcelTable _
         LinkedToExcel:=False, _
         WordFormatting:=False, _
         RTF:=True
    .End = .End + 1
    With .Tables(1)
        .Range.Font.Size = 8
        .AutoFitBehavior (wdAutoFitWindow)
        .AllowAutoFit = False
        .Columns.DistributeWidth
    End With
End With

As for the error - objDoc.Tables(3).Range.Font.Size = 8

Upvotes: 2

Related Questions