New landscape Word doc from Excel

[kilgour]

Hi, could someone please tell me how, using VBA in Excel, I can create a new Word Document then make the page layout landscape then paste in a selection from the Excel document.

Many thanks,

Paul

[mdmackillop]

Create a word template in landscape and open that to receive your data.

Here's some sample code I use to copy from Excel into Word, pass values to the document properties and then run Word macros to do the formatting.


Sub CopyToWord()
Dim wd
Dim Data As Range
Dim MyRange
Dim Doc
Dim Footer As String
Dim BillName As String
Dim BillNo As String
Footer = [I1] 'InputBox("Footer text", , "PQ 1234 Sample Text")
BillName = [I2] 'InputBox("Bill Name", , "BILL NO. 3 TESTING BILL")
BillNo = [I3] 'InputBox("Bill Number", , "3")

Set Data = Range(Cells(2, 2), Cells(2, 2).End(xlDown)).Resize(, 3)
Data.Copy
Set wd = CreateObject("Word.Application")
wd.Visible = True
Set Doc = wd.Documents.Add(Template:="S:\Templates\Qs\CatoBillUpdate.dot", NewTemplate _
:=False, DocumentType:=0)
wd.Selection.Paste
wd.Application.Run MacroName:="FixCatoLayout"
wd.Selection.HomeKey Unit:=6

SetProperty Doc, "Title", Footer
SetProperty Doc, "Subject", BillName
SetProperty Doc, "Author", BillNo
wd.Application.Run MacroName:="UpdateAll"

End Sub

Sub SetProperty(Doc, PropName As String, PropVal)
Dim Prop
Dim BProps
Set BProps = Doc.BuiltinDocumentProperties
Set Prop = BProps(PropName)
Prop.Value = PropVal
End Sub