vba to copy data from Excel to Word

[Chris Williamson]

When you click on a command button what would the vba code be to copy the data from the active excel workbook active sheet so that it ends up in the active word document?

I want the values from:
Cell A1 value in Excel to bookmark name "Text1"
Cell A2 value in Excel to bookmark name "Text2"
Cell A3 value in Excel to bookmark name "Text3"
to be copied into the active word document.

Thank you

 

[VishMishra]

Hi See... if this article helps you...

otherwise let me know

http://www.learnexcelmacro.com/2012/01/copy-from-excel-and-paste-in-word/

 

[Chris Williamson]

I was thinking something like this...

Sub test()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
Dim objWord As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Open "C:\Users\Acer Windows 7\Desktop\test.docx"
With objWord.ActiveDocument
  .Text1.Value = ws.Range("A1").Value
  .Text2.Value = ws.Range("A2").Value
  .Text3.Value = ws.Range("A3").Value
End With
End Sub

The 'Text1', 'Text2' and 'Text3' are Bookmarks within the word document, this code gets the word document open but it gets stuck at at the point of copying the data from excel to word.

 

[VishMishra]

How to copy paste in withing a particular book mark.. I am not sure. If i get surely post it.

 

[Chris Williamson]

Yeah I know, it is tricky... this particular problem has been giving me headache for ages now.

 

[energman58]

This guy solved this by using Replace in Word:

http://stackoverflow.com/questions/6310258/pasting-from-excel-into-a-word-document

Basically he put the locations to paste the data to as unique nonsense words and replaced them with the cell contents. I guess if you do this you need to keep the original version of your document file as a "master" and save the active version as a new name but that shoudln't be too hard to do. I think how you are trying to do it will always fail (although intuitively it feels like it should work) and every other method I can think of appends the data at the end of the word doc or at the begining of the file overwriting what is there.

 

[Norie]

Chris

It's not that tricky and the code you posted is almost there, you just need to change how you are referring to the bookmarks.

Try this.

Sub test()
Dim objWord As Object
Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    Set objWord = CreateObject("Word.Application")

    objWord.Visible = True

    objWord.Documents.Open "C:\test.docx" ' change as required

    With objWord.ActiveDocument
        .Bookmarks("Text1").Range.Text = ws.Range("A1").Value
        .Bookmarks("Text2").Range.Text = ws.Range("A2").Value
        .Bookmarks("Text3").Range.Text = ws.Range("A3").Value
    End With
 
    Set objWord = Nothing

End Sub

 

[Chris Williamson]

Norie,

A million thank you's my friend.

However, there is a very small amendment needed I think...
The data from cell A1 (ms excel) gets copied into bookmark Text1 (ms word) just fine.

But there is a: (Run-time error '6028': The range cannot be deleted) for the 2nd and 3rd values?

Any ideas to get all three values copied across?

Chris

 

[Norie]

Chris

Sounds like it's something to do with the Word document.

Perhaps something to do with the bookmarks and how/where they've been set?

I just added 3 simple bookmarks, 4 or 5 lines apart, to a blank document.

Does your document have anything in it?

 

[Chris Williamson]

Cheers Norie, you are right, it was a query with the word document.

I've added 3 new bookmarks and renamed them to something else, and all three of them work just fine the second time round.

Spot on mate.