Excel table, paste to word

[nickdel]

The following code creates a new word document. I have 2 lots of data from Excel that I need to put on the word document so I thought best solution would be to copy and paste. The code should take the first lot of cells I want to copy and put these on the document, then insert a page break, copy the second lot of cells and then put these on the new page.

What actually happens is it overwrites the original paste!

Dim wdApp As Word.Application
Dim wdDoc As Word.Document

Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Add

wdApp.Visible = True

Range("a1:a2").Copy

With wdDoc
Range("a1:a2").Copy
    .Content.Paste
    .Content.InsertParagraphAfter
    .Content.InsertAfter ("")
    
Range("a3:a4").Copy
    .Content.Paste
End With

Any idea's?

Nick

 

[Bong]

What if you insert a
Selection.EndKey(Unit:=wdStory, Extend:=wdMove)
between
Range("a1:a2").Copy
.Content.Paste
.Content.InsertParagraphAfter
.Content.InsertAfter ("")

and
Range("a3:a4").Copy
.Content.Paste


_________________
Bob Rashkin

 

[nickdel]

Hi Bob, its not valid syntax.

Nick

 

[Bong]

Oh, right. Your macro is in Excel. Then maybe
[red].[/red]Selection.EndKey(Unit:=wdStory, Extend:=wdMove)


_________________
Bob Rashkin

 

[nickdel]

No I tried that also, .selection is not valid within wddoc.

 

[nickdel]

Got this working using ranges:

Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim lastp As Range
 
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Add
 
wdApp.Visible = True

 
With wdDoc
    Range("a1:a2").Copy
        Set Range2 = .Content
        Range2.Collapse Direction:=wdCollapseEnd
        Range2.Paste
    
    Range2.Collapse Direction:=wdCollapseEnd
    Range2.InsertBreak Type:=wdPageBreak
  
    Range("a3:a4").Copy
        Set Range2 = .Content
        Range2.Collapse Direction:=wdCollapseEnd
        Range2.Paste
    
End With

May write an FAQ on this as there doesn't appear to be anything out there!

 

[fumei]

.selection is not valid within wddoc."

True, because Selection is an application child object, NOT a document child object.

So wdDoc.Selection does not work, but wrdApp.Selection does.

Also, care must be taken when declaring Range objects when working from Excel to Word.

Dim rng As Range

declares an Excel Range.

Dim rng As Word.Range

declares a Word Range. I had been messed up by that in the past.

faq219-2884

Gerry
My paintings and sculpture