Excel range not pasting to Word Document Help

[newbee2]

Hi,
I want to take a range from an excel sheet and paste it to a new word document laid out in landscape. I am using the following. I can get word to open but no document shows.

Case Is = 8
MsgBox "it works", vbOKOnly
Set rng7 = Sheet1.Range("a130:g154&quot
ThisWorkbook.Sheets(1).ActiveRange.Copy
WordStart

Sub WordStart()
On Error Resume Next
'create a word instance to use for the Table uses
'the existing word instance if there is one, otherwise creates
'a new instance.
Dim objWord As Object
Dim fisWordRunning As Boolean

Set objWord = GetObject(, "Word.Application&quot
If Err.Number = 429 Then
fisWordRunning = False
'word is not running; create a Word object
Set objWord = CreateObject("Word.Application&quot
Err.Clear
Else
'use current word object
fisWordRunning = True
End If
With objWord
'add new document
.Documents Add
.Documents.PageSetup.Orientation = wdOrientLandscape
'make word visible
.Visible = True
End With

With objWord.Selection
.EndKey Unit:=wdStory
'Paste Range
.Paste
End With
'release object Variable
Set objWord = Nothing

End Sub

Where be I going wrong
Regards
Bill

 

[cthaxter]

You don't need to make sure Word is not already open. Use this as your WordStart sub instead:

Dim objWord As Word.Application
Set objWord = CreateObject("Word.Application&quot
With objWord
'add new document
.Documents.Add
.Documents(.Documents.Count).PageSetup.Orientation = wdOrientLandscape '
'make word visible
.Visible = True
End With
With objWord.Selection
.EndKey Unit:=wdStory
'Paste Range
.Paste
End With

 

[newbee2]

Thanks for the advise, have placed into module.
still getting word opening and greyed screen, no document showing.
Thanks
Bill

 

[cthaxter]

It works fine for me. What version of Office are you using?

I wonder if the document is being created, just not shown; or if the document is not being created at all.

If the entire code runs fine, then the document is being created, otherwise you wouldn't be able to modify it.

In this case, try saving it and then opening it again.

Also, you may want to put your new document object in a variable for purposes of ease of coding. And maybe put the .visible line earlier. Try this instead of my previous suggestion:


Dim objWord As Word.Application
Dim objDoc As Word.Document
Set objWord = CreateObject("Word.Application&quot
With objWord
'add new document
.Documents.Add
'make word visible
.Visible = True
Set objDoc = .Documents(.Documents.Count)
objDoc.PageSetup.Orientation = wdOrientLandscape '
End With
With objWord.Selection
.EndKey Unit:=wdStory
'Paste Range
.Paste
End With
objDoc.SaveAs "C:\testword.doc" 'full path
objDoc.Close
objWord.Documents.Open "C:\testword.doc" 'full path

 

[cthaxter]

Modification to my other post from earlier today:

Use this instead of the

With objWord

block:

With objWord
'make word visible
.Visible = True
'add new document
.Documents.Add
Set objDoc = .Documents(.Documents.Count)
objDoc.PageSetup.Orientation = wdOrientLandscape '
End With