Copying an excel File and putting it into Word

[coleyoder]

I am currently trying to copy an excel worksheet and paste it into a word document and turn it horizontally.

I know about to create a text box and turning it to be horizontal then copying special the excel sheet in.

But when doing that the excel spreadsheet is too big to fit on the whole page and it won let me shrink it to fit on the page and that is the main thing I need to do.

Any advice would be great thank you!

 

[Macropod]

You could, for example, use a landscape page layout. Try something based on:

Sub Excel_to_Word()
'Note: This code requires a reference to the Word Object Library to be set.
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim xlRng As Excel.Range, r As Long, c As Long, FlNm As String
With ActiveWorkbook
  FlNm = Split(.FullName, ".xls")(0) & ".docx"
  With .ActiveSheet
    With .UsedRange.Cells.SpecialCells(xlCellTypeLastCell)
      r = .Row
      c = .Column
    End With
    Set xlRng = .Range(.Cells(1, 1), .Cells(r, c))
  End With
End With
With wdApp
  .Visible = False
  Set wdDoc = .Documents.Add
  With wdDoc
    .PageSetup.Orientation = wdOrientLandscape
    xlRng.Copy
    .Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
    .SaveAs Filename:=FlNm, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
    .Close False
  End With
  .Quit
End With
Application.CutCopyMode = False
Set xlRng = Nothing: Set wdDoc = Nothing: Set wdApp = Nothing
End Sub

If you need to have the rest of the document in portrait format, you'll need to insert 'Next Page' Section breaks before and/or after wherever you want to insert the table, specifying the location of both the Section break(s) and the destination to paste to; the above code doesn't do any of that.

Note: For testing, you might want to change '.Visible = False' to '.Visible = True'.