Set the page orientation when exporting an Excel table to Word

Question

I have been struggling with the attached VBA code which tries to set the page orientation when exporting an Excel table to Word. It is easy enough to do for one table, but I am trying to export several tables some that should be portrait and others landscape. Switching from one format to the other within the new Word document is the issue. I have tried various ways to add section breaks, but I have failed to do anything close to what I want.

The snippet below is run in a loop that exports each table as the form's control's are checked for a selection.

The code depends on a user's selections on a form so they may differ each time. To be more easily read I have only included the code sections which actually deal with the new Word document (newDoc) formatting. What is shown is the last incarnation of the code, there have been many versions.

What I need is a way to run the loop (and the attached code) so that each table is oriented according to its predetermined needs, either portrait or landscape in the new Word document.

Select Case intDoc  ' set the Word pages based on the selected document
                Case 1, 4, 5, 6 ' Estimate(1), Invoice(4), Scope of Work-->Orientation = wdOrientPortrait
                    ' paste the table into the newDoc and set some options
                    With newDoc
                        .ActiveWindow.View.ShowAll = False ' Hide all formatting marks
                        .ActiveWindow.View.ShowHiddenText = False ' Hide all  hidden text
                        .Paragraphs(.Paragraphs.Count).Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=True
                        .Content.InsertParagraphAfter
                        .Range(.Content.End - 1).InsertBreak Type:=wdSectionBreakNextPage   'wdPageBreak
                        With .PageSetup
                            .Orientation = wdOrientPortrait
                            .RightMargin = Application.InchesToPoints(0.75)
                            .LeftMargin = Application.InchesToPoints(0.75)
                        End With
                     End With
                    ' Autofit the table to the page
                     Set wordTbl = newDoc.Tables(newDoc.Tables.Count)
                     wordTbl.AutoFitBehavior (wdAutoFitWindow)

            Case 2, 3  ' the Detail listing (2), or the Itemized listing(3)-->Orientation = wdOrientLandscape
                ' paste the table into the newDoc and set some options
                With newDoc
                    .ActiveWindow.View.ShowAll = False ' Hide all formatting marks
                    .ActiveWindow.View.ShowHiddenText = False ' Hide all  hidden text
                    .Paragraphs(.Paragraphs.Count).Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=True
                    .Content.InsertParagraphAfter
                    .Range(.Content.End - 1).InsertBreak Type:=wdSectionBreakNextPage   'wdPageBreak
                    With .PageSetup
                        .Orientation = wdOrientLandscape
                        .RightMargin = Application.InchesToPoints(0.5)
                        .LeftMargin = Application.InchesToPoints(0.5)
                    End With
                 End With
                ' Autofit the table to the page
                 Set wordTbl = newDoc.Tables(newDoc.Tables.Count)
                 wordTbl.AutoFitBehavior (wdAutoFitWindow)
        End Select

Unfortunately, you don't really ask a question, or describe exactly what the problem is with what you've attempted. Could you please use the edit link to add a description of the result your getting and the result you want. — Cindy Meister
I think it is clear (but I have been living this issue), but then you want a question: How do you add several Excel tables to a new Word document (one document) so that each has it's own page orientation as required by the table? — Richard Barnes

Cindy Meister Cindy Meister · Accepted Answer · 2018-10-05T13:59:35

Since you haven't provided a [mcve] I'm not able to work directly with your code. Below is my test code - you should be able to adapt it to your exact scenario. There'd be more than one way to approach the task, but I've tried to stay as close to your original approach as possible.

Note that I've added the object variable rngNewTable - a Word.Range object. This helps better track exactly where things need to happen in the document (same as in Excel, really). Document.Content provides a good reference Range, but it's not "adaptable" to identify a particular point or area.

I've also added a variable to store the current page orientation. There's no need to repeat certain actions if the orientation is already correct, so this is used to test the current orientation. (Actually, you wouldn't even need separate sections for each table if the orientation doesn't change - but I don't know the complete logic of what's going on, so haven't changed that.)

Pasting the table is now at the end of each loop and could be outside the Select, but I leave that decision to you. You'll also need to replace my For...Next with your own logic.

The way this basically works is to collapse / set the end of the target Range after each insertion (whether of table or section break) so that it's always after the new material.

Sub InsertTableWithPageOrientation()
    Dim newDoc As Word.Document
    Dim intDoc(3) As Long, iCounter As Long
    Dim rngNewTable As Word.Range
    Dim iCurrPageOrientation As Long
    Dim wordTbl

    Set newDoc = ActiveDocument
    With newDoc
        .ActiveWindow.View.ShowAll = False ' Hide all formatting marks
        .ActiveWindow.View.ShowHiddenText = False ' Hide all  hidden text
        Set rngNewTable = newDoc.content
        rngNewTable.Collapse wdCollapseEnd
        iCurrPageOrientation = .PageSetup.Orientation
    End With
    intDoc(0) = 1
    intDoc(1) = 2
    intDoc(2) = 3
    intDoc(3) = 4
    For iCounter = LBound(intDoc) To UBound(intDoc)
        rngNewTable.InsertBreak Type:=wdSectionBreakNextPage   'wdPageBreak
        rngNewTable.Start = newDoc.content.End
        Select Case intDoc(iCounter)  ' set the Word pages based on the selected document
            Case 1, 4, 5, 6 ' Estimate(1), Invoice(4), Scope of Work-->Orientation = wdOrientPortrait
                ' paste the table into the newDoc and set some options
                If iCurrPageOrientation <> wdOrientPortrait Then
                    With rngNewTable.Sections(1).PageSetup
                        .Orientation = wdOrientPortrait
                        .RightMargin = Application.InchesToPoints(0.75)
                        .LeftMargin = Application.InchesToPoints(0.75)
                        iCurrPageOrientation = wdOrientPortrait
                    End With
                End If
                rngNewTable.PasteExcelTable LinkedToExcel:=false, WordFormatting:=False, RTF:=True
                rngNewTable.Start = newDoc.content.End
            Case 2, 3  ' the Detail listing (2), or the Itemized listing(3)-->Orientation = wdOrientLandscape
                ' paste the table into the newDoc and set some options
                If iCurrPageOrientation <> wdOrientLandscape Then
                    With rngNewTable.Sections(1).PageSetup
                        .Orientation = wdOrientLandscape
                        .RightMargin = Application.InchesToPoints(0.5)
                        .LeftMargin = Application.InchesToPoints(0.5)
                        iCurrPageOrientation = wdOrientLandscape
                    End With
                End If
                rngNewTable.PasteExcelTable LinkedToExcel:=false, WordFormatting:=False, RTF:=True
                rngNewTable.Start = newDoc.content.End
        End Select
    Next
    ' Autofit the table to the page
    Set wordTbl = newDoc.Tables(newDoc.Tables.Count)
    wordTbl.AutoFitBehavior (wdAutoFitWindow)
End Sub

Set the page orientation when exporting an Excel table to Word

1 Answers