Copying Multiple Excel ranges to Word doc

stroffso

Board Regular
Joined
Jul 12, 2016
Messages
125
Hi All,
I have a number of ranges which I would like to paste into word doc, when i do one it works fine however on the 2nd range it doesnt seem to work. So ideally it would paste the first range, then do a few spaces then the second range and so on. Would be good to have the page in landscape too. Below is the code I used.


Sub Excel_to_Word()
Dim appWord As Word.Application


Set appWord = New Word.Application


appWord.Visible = True


Range("C11:H13").Copy
appWord.Documents.Add.Content.Paste


Range("C20:L141").Copy


appWord.Documents.Add.Content.Paste


End Sub
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,445
Try something based on:
Code:
Sub Excel_to_Word()
Dim wdApp As New Word.Application, wdDoc As Word.Document
With wdApp
  .Visible = True
  Set wdDoc = .Documents.Add
  With wdDoc.Range
    ActiveSheet.Range("C11:H13").Copy
    .Characters.Last.Paste
    .InsertAfter vbCr & vbCr
    ActiveSheet.Range("C20:L141").Copy
    .Characters.Last.Paste
  End With
  .Activate
End With
Set wdDoc = Nothing: Set wdApp = Nothing
End Sub
 
Last edited:

stroffso

Board Regular
Joined
Jul 12, 2016
Messages
125

ADVERTISEMENT

Thanks but it returns an error, says it doesnt support this property or method, relating to the following row in your code.

I tried this instead and it seems to do the trick, thanks for your help

wdDoc.Content.InsertParagraphAfter
 

stroffso

Board Regular
Joined
Jul 12, 2016
Messages
125
That is inefficient compared to the revision I posted.

I had posted that without seeing your update. Both seem to do exactly the same thing so all good, Do you know is there then a way of shrinking everything you have pasted to fit the doc? Currently they are all going way outside the margins?

thanks
 
Last edited:

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,445
You could change the paste format, so you paste an Excel worksheet object rather than a Word table. Word should auto-scale that to fit within the left/right margins. The potential downside is that an embedded Excel worksheet object cannot span a page boundary.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,719
Messages
5,524,460
Members
409,582
Latest member
riibuildersinc

This Week's Hot Topics

Top