Code for changing word from portrait to landscape

[Eurekaonide]

Hi All

I wonder if you can help please, I have some code built in Excel which opens word and copies and pastes some data into a word document. The thing is I need to change the layout in word from portrait to landscape but do not seem to be able to do this, I have used ActiveDocument.PageSetup and the orientation but this seems to come back as an error - any ideas?

Many Thanks

[lucas]

Welcome to the board

Do you create a new word document?

Have you considered using a template?

Could you post the code you are using and running from excel?

When posting code, select it and hit the vba button to format it for the forum.

You can also attach your excel file by hitting go advanced and scroll down till you see the manage attachments button.

[Eurekaonide]

Hi, Thanks for your help with this.

Yes it is a new Word Document, not a template.

Private Sub CmdRepWord_Click()

'Message Box pops up to advise about re-sizing if needed once in Word
MsgBox "The Picture of your selection will now appear in MS Word." & vbCrLf & "This is set for the width of the whole Risk Report (A:W)," & vbCrLf & "however you can resize accordingly once within Word, by clicking on the Picture and altering its size."
Application.ScreenUpdating = False
Dim ObjWord As Object, WrdDoc As Object
Set ObjWord = CreateObject("Word.Application")

ObjWord.Visible = True
Set WrdDoc = ObjWord.Documents.Add
Set WrdDoc = ActiveDocument.PageSetup

'Get the address, or reference, from the RefEdit control.
Addr = RefEdit1.Value
If RefEdit1.Value = "" Then MsgBox "Need to Select Data"
On Error Resume Next

'Set the SelRange Range object to the range specified in the
'RefEdit control.
Set SelRange = Range(Addr)

'Copy the range as a piicture
SelRange.CopyPicture , xlPicture

'Paste
ObjWord.Selection.PasteAndFormat (wdPasteDefault)

'Clean Up
Set WrdDoc = Nothing
Set ObjWord = Nothing

'Unload user Form
Unload Me

Application.ScreenUpdating = True
End Sub

[lucas]

could you possibly attach your excel file with fake info so I don't have to try to figure out and re-create a userform and controls that you already have in your posession?

[Eurekaonide]

Its quite a large doc :( . I am thinking however that a template maybe a good option as later I need to have the standard format headers and footers etc that our company uses. Do you therefore know what I would use to ref to a specific template please?

[lucas]

Here's one way:

dim path as string
Dim wb As Excel.Workbook
dim docword as object
Set wb = ActiveWorkbook
Path = wb.Path & "\pushmerge.dot"

Set docWord = pappWord.Documents.Add(Path)
docWord.Bookmarks("bookmark").Range.Text = your range here

[lucas]

Here's a KB entry that might help. There are probably more if you search.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=381

[Eurekaonide]

OK, Thanks for your help on this - its still not working - it returns the error 424 Object Required on the line


Dim Path As String
Dim ObjWord As Object, WrdDoc As Object
Set ObjWord = CreateObject("Word.Application")
Dim pappword As Object
Path = wb.Path & "\Word Report R&I.doc"

ObjWord.Visible = True



Path = wb.Path & "Word Report R&I.doc"

Any suggestions this is sending me CRAZY !!! :banghead:

[lucas]

It doesn't know what wb is.

you need to add this before the line with the error:


Dim wb As Excel.Workbook
Set wb = ActiveWorkbook

[Eurekaonide]

Thank you so very much - Its all working now :rotlaugh: :rotlaugh:

[lucas]

If you have your solution, please mark your thread solved using the thread tools at the top of the page.

That keeps others from reading the entrire thread just to find it's been solved.