Forcing a table to fit the margins

Question

the code suggested in the answer to this question (Exporting a sheet to a word table gives a weird outcome) does not make the table fit in the margins, resulting in part of the table being hidden

How can I force the table to fit in the margins? I have tried objDoc.Tables.AutoFitBehavior (wdAutofitWindow) but it doesn't work.

Answer 1

Sub Export_Click()
    Dim objWord As Word.Application
    Dim myDoc As Word.Document
    Dim myTable As Word.Table
    Dim myRange As Excel.Range
    Dim lastRow As Long

    Set objWord = CreateObject("Word.Application")
    objWord.Visible = True

    Set myDoc = objWord.Documents.Add

    lastRow = Sheets("export").Range("$G$1").Value  'number of lines to export

    Set myRange = Range("A1:F" & lastRow)
    myRange.Copy

    myDoc.Paragraphs(1).Range.PasteExcelTable _
        LinkedToExcel:=False, _
        WordFormatting:=False, _
        RTF:=False

    Set WordTable = myDoc.Tables(1)
    WordTable.AutoFitBehavior (wdAutoFitWindow)

    Application.CutCopyMode = False         'clear the clipboard
End Sub

This code is based on your last question.

For above code to run you'll have to add reference of Microsoft Word 12.0 Object Library. You can do this in VBE. In Tools menu click References and select Microsoft Word 12.0 Object Library