VBA Word to Excel - Word embeds inexcel when trying to paste

0
votes

I am trying to copy from a word document to a excel document, when I do this with the code below it embeds the word document into the excel document rather than paste in the text.

How can I make it so it does not embed the word document but pastes in the text instead? 

Dim objWord As Object, objDoc As Object
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open("\\Desktop\REPORT.doc")
objWord.Visible = True

objWord.Selection.WholeStory
objWord.Selection.Copy

Workbooks.Add
Selection.PasteSpecial
2
vbaexcelms-word
do you have a picture of what is going on? I suspect you just need a paste as values, but I'd like to confirm first – Bryan Davies
@BryanDavies As much as I would like to upload a picture of what is happening, I cant, work has printscreen blocked/locked. Best way I can describe it is, the whole word document is in excel like a text box but it has the ruler on the side and top, in excel if I select the embeded word it has in the formula bar of excel "=EMBED("Word.Document.8","")" – Mr.Burns
Alright, I assume you need this quickly :). I'm on my mobile, but if you use record macro and record a special paste as values, you will get a code that will work. I'll update once I'm back in a computer – Bryan Davies
@BryanDavies, this isnt urgent but I will see what I get for the paste as values – Mr.Burns
@BryanDavies, just tried paste as values, works fine, post an answer and I'll accept it, ty – Mr.Burns

2 Answers

2
votes

When you paste from Word to Excel without specifying the paste method, VBA defaults to inserting a Word object into the sheet (as this preserves the most data possible). If you specify in the PasteSpecial command to use xlPasteValues, it will insert the selected text into the desired cell as a string

2
votes

Try Paste as values;

Selection.PasteSpecial Paste:=xlPasteValues