Consulting

Results 1 to 6 of 6

Thread: Vba to copy excel worksheet orientation to word document

  1. #1

    Vba to copy excel worksheet orientation to word document

    Hi,

    I have an excel workbook with 30+ worksheets. I have code which copies each worksheet into one new word document but my issue is that each worksheet has a different layout orientation whether it be landscape or portrait and i need that orientation to transfer to the word document as well. So the word document will be a combination of portrait and landscape layouts. Any ideas?

  2. #2
    are you meaning landscape as in the pagesetup.orientation property of the worksheet? or just the shape of data on the sheet?

    to determine the shape of data, you probably need to sum the widths of the used columns and height of used rows, then compare to see which is greater

  3. #3
    So each worksheet within the the workbook is set with the page setup property of either xlportrait or xllandscape. What I need is when my code copies every worksheet to the new word document that if the worksheet is xlLandscape = wdOrientLandscape and if its xlPortrait then in word its wdOrientPortrait. This is the code im using to copy it over.

    Application.ScreenUpdating = False


    Set wdApp = New Word.Application
    Set wdDoc = wdApp.Documents.Add


    For Each ws In ActiveWorkbook.Worksheets
    Application.StatusBar = "Copying data from " & ws.Name & "..."
    ws.Range("Print_Area").Copy
    wdDoc.paragraphs(wdDoc.paragraphs.Count).Range.InsertParagraphAfter
    wdDoc.paragraphs(wdDoc.paragraphs.Count).Range.PasteExcelTable False, False, True
    Application.CutCopyMode = False
    wdDoc.paragraphs(wdDoc.paragraphs.Count).Range.InsertParagraphAfter

    If Not ws.Name = Worksheets(Worksheets.Count).Name Then
    With wdDoc.paragraphs(wdDoc.paragraphs.Count).Range
    .InsertParagraphBefore
    .Collapse Direction:=wdCollapseEnd
    .InsertBreak Type:=wdPageBreak
    End With
    With wdApp.ActiveDocument.PageSetup
    .TopMargin = wdApp.InchesToPoints(0.6)
    .BottomMargin = wdApp.InchesToPoints(0.6)
    .LeftMargin = wdApp.InchesToPoints(0.6)
    .RightMargin = wdApp.InchesToPoints(0.6)
    End With





    End If




    Next ws
    Set ws = Nothing
    Application.StatusBar = "Cleaning up..."


    wdApp.Visible = True

    Application.StatusBar = False




    End Sub

  4. #4
    as paragraphs have no orientation, you need to add a new section each time you want to change the orientation within the document
    each section has its own pagesetup

    try like
    wddoc.Sections.Add
    wddoc.Sections(wddoc.Sections.Count).PageSetup.Orientation = ws.pagesetup.orientation - 1
    you can test if the current section is the same as the worksheet and only add new section when required, or add new section for each worksheet regardless

  5. #5
    Ahh Hah! Works great now! Thanks westconn1

  6. #6
    pls mark solved

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •