Paste Excel Range into a word doc

[rhombus4]

Hi,

Need to copy Range from excel and paste into a word document. will probably have to copy 2 or 3 ranges so may have to repeat the process

Sub CopyRange()
Range ("MyDatatoCopy")
Dim wrdApp, wrdDoc
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.documents.Open("C:\MyDocs\Test\Myworddoc.docx", ReadOnly:=True)
wrdApp.Visible = True

Selection.Goto What:=wdGoToBookmark, Name:="Bookmark1"
    With ActiveDocument.Bookmarks
       .DefaultSorting = wdSortByName
        .ShowHidden = True
    End With
    Selection.Paste
End Sub

 

[Macropod]

Try something based on:

Sub CopyRanges()
'Note: A VBA reference to the Word library is required
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim ArrCopyRefs(), ArrPasteRefs(), i As Long
ArrCopyRefs = Array("ExcelRange1", "ExcelRange2", "ExcelRange3")
ArrPasteRefs = Array("WordBookMark1", "WordBookMark2", "WordBookMark3")
With wdApp
  .Visible = True
  Set wdDoc = .Documents.Open("C:\MyDocs\Test\Myworddoc.docx", AddToRecentFiles:=False, ReadOnly:=True)
  With wdDoc
    For i = 0 To UBound(ArrCopyRefs)
      ActiveSheet.Range(ArrCopyRefs(i)).Copy
      .Bookmarks(ArrPasteRefs(i)).Range.Paste
    Next
  End With
End With
End Sub

 

[rhombus4]

Thanks Paul

I did find a different way but your code looks alot better especially with multiple ranges

 

[rhombus4]

Try something based on:

Sub CopyRanges()
'Note: A VBA reference to the Word library is required
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim ArrCopyRefs(), ArrPasteRefs(), i As Long
ArrCopyRefs = Array("ExcelRange1", "ExcelRange2", "ExcelRange3")
ArrPasteRefs = Array("WordBookMark1", "WordBookMark2", "WordBookMark3")
With wdApp
  .Visible = True
  Set wdDoc = .Documents.Open("C:\MyDocs\Test\Myworddoc.docx", AddToRecentFiles:=False, ReadOnly:=True)
  With wdDoc
    For i = 0 To UBound(ArrCopyRefs)
      ActiveSheet.Range(ArrCopyRefs(i)).Copy
      .Bookmarks(ArrPasteRefs(i)).Range.Paste
    Next
  End With
End With
End Sub

Finally managed to try it but when I run I get - Compile Error User defined type not defined on this line
Dim wdApp As New Word.Application, wdDoc As Word.Document

When I change it to Dim wdapp As Object: Dim wddoc As Object

It highlights .Visible = True and says Object variable or with block variable not set

 

[Macropod]

Did you read the comment at the top of the code and set the required reference?

 

[rhombus4]

I'm limited to what add ins/add ins I can set
Is there another way without the VBA reference

 

[Macropod]

I'm limited to what add ins/add ins I can set

A curious claim; I can't imagine what limitations would prevent you adding a reference to an Office component...

Is there another way without the VBA reference

Of course! Simply initiate Word with late binding the way your own code does:

Dim wdApp As Object, wdDoc As Object
Set wrdApp = CreateObject("Word.Application")
…
  Set wdDoc = .Documents.Open "C:\MyDocs\Test\Myworddoc.docx", , True, False

PS: It makes no sense to me why you'd make a document you're writing to ReadOnly, but that's what your original code does and mine replicates.

 

[rhombus4]

Only reason it's read only is so that more than 1 user can use the word doc at a time

Regarding add-ons/addins it's what we use at work and different users will use the excel file.

As I don't have admin rights I wasn't sure I could use the addin, and if I did use it, would other users have to add the VBA reference to their library on their PCs or would it work automatically ??

 

[Macropod]

Regarding add-ons/addins it's what we use at work and different users will use the excel file.

As I don't have admin rights I wasn't sure I could use the addin, and if I did use it, would other users have to add the VBA reference to their library on their PCs or would it work automatically ??

You really do need to spend a bit of time learning Office VBA and its terminology. Setting a reference to an Office component has nothing to do with an addin. Furthermore, once set for the project, the reference applies for whoever uses that project.

 

[rhombus4]

Ok thanks for your help Paul