Excel VBA copy and paste selected text from word

Question

I want a macro button on my Excel sheet that:

I will highlight (select) some text in a word document
I press the button
the text is then copied and pasted into a specified cell in my excel sheet

I have tried:

sub paste()
    objword.selection.copy range("B2")
End sub

Firstly I don't know whether objword is the right syntax, and secondly everytime I select text in word, I cannot click on excel without the text deselecting, so how will I be able to do this? Do I have to create the macro in word?

Thanks

Have a look here. This should get you started. stackoverflow.com/questions/21317885/… — Jelmer
@Jelmer so should I be recording a macro in word and then referencing the excel file from there? I can't get a selection from word in an excel macro because clicking on excel deselects the 'selection'. — newbie23
@Pᴇʜ because it would only require 2 actions : highlight and click button. — newbie23
@newbie23 Much and complicated work for such a tiny "improvement". I bet you could do Ctrl+C/V for the next 10 years and still save time compared to writing that code. — Pᴇʜ

CLR CLR · Accepted Answer · 2019-01-25T09:41:30

The following code (requiring a reference to Microsoft Word xx Object Library) running in Excel will grab the current selection in Word and copy the text into A1:

Sub paste()
    Dim oWd As Word.Application
    Set oWd = GetObject(, "Word.Application")
    ActiveSheet.Cells(1, 1) = oWd.Selection
    Set oWd = Nothing
End Sub

Also, the following is the equivalent opposite, i.e. code that can run in Word that takes the selection in Word and sends it to Excel:

Sub paste()
    Dim oXL As Excel.Application
    Set oXL = GetObject(, "Excel.Application")
    oXL.ActiveSheet.Cells(1, 1) = Selection.Text
    Set oXL = Nothing
End Sub

This one requires a reference to Microsoft Excel xx Object Library.

Excel VBA copy and paste selected text from word

1 Answers