Excel to Word to be Landscape

[petars87]

Hi people,
can u help me to I change Word to be Landscape

 Sub CopyToWord()
     Dim objWord As Object
     Dim objDoc As Object
     Set objWord = CreateObject("Word.Application")
     Set objDoc = objWord.Documents.Add(Template:= _
         "C:\XXX\Desktop\template.docx")
         
     Set Ar = Worksheets("Sheet1").Range("A1:F" & Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row)
    
     Ar.Copy
     
     objDoc.Range.Paste
    
    
With objDoc
    .PageSetup.Orientation = wdOrientLandscape
    End With
     
     objWord.Visible = True
     Set objDoc = Nothing
     Set objWord = Nothing
 End Sub

Thank you.

 

[Macropod]

To use named constants with late binding, you need to tell your code what their values are. For example:

Sub CopyToWord()
Dim objWord As Object, objDoc As Object
Const wdOrientPortrait As Long = 0
Const wdOrientLandscape As Long = 1
Set objWord = CreateObject("Word.Application")
With objWord
  Set objDoc = .Documents.Add(Template:="C:\XXX\Desktop\template.docx")
         
  With Worksheets("Sheet1")
    .Range("A1:F" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy
  End With
     
  With objDoc
    .Range.Paste
    .PageSetup.Orientation = wdOrientLandscape
  End With
     
  .Visible = True
End With
Set objDoc = Nothing: Set objWord = Nothing
End Sub

Better still would be to use a template that already had the desired orientation - then you wouldn't have to change it in code.

 

[petars87]

Thank you Macropod, it works nice, u saved my time..

Can u help me with one more question please:

How to, in this code add part to insert PageBrake after word "Name"

 

[Macropod]

There's no indication in anything you've posted regarding "Name", so it's far from obvious how that should be addressed. You will need to give more details.

PS: It's midnight here, so I won't be replying for ~8hrs.

 

[petars87]

That word is in this range

With Worksheets("Sheet1")
    .Range("A1:F" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy
  End With

 

[petars87]

Plese don't spend time on this, I posted again.

Thank you a lot.

 

[Joe4]

Plese don't spend time on this, I posted again.

Please continue in this thread, as that thread has been locked, as per forum rules.

 

[Macropod]

That word is in this range

With Worksheets("Sheet1")
    .Range("A1:F" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy
  End With

So what do you want to do - split the table or just make sure the rows don't get split across a page break? Do you realize that, if you split the table, any header rows in your data won't appear on the new table whereas, without splitting the table, Word's 'repeat header rows' function can make the table header appear on all pages the table spans? Does the word 'Name' appear more than once in the table? If so, how is a macro to determine which instance to use? Is the word 'Name' at the start or end of a row, or is somewhere along the row? If it's at the start or somewhere along the row, do you want to split the table at that point, or at the end of the row?