Question: Copying data from Word document for PASTE to Excel

MERLIN469

New Member
Joined
May 8, 2003
Messages
1
I've inserted a Word document as an OBJECT in my spreadsheet and am trying to do the equivalent of a SELECT ALL, COPY so I can paste the data to the spreadsheet directly.
The problem is, as soon as the Word Object is Selected (i.e Double Clicked), WORD becomes the Active Application (even within Excel)

Presently, I have the user EDIT/SELECT ALL and then EDIT/COPY and then click a command button in Excel to do the rest. (BTW, a WORD macro cannot be attached to the WORD document being loaded.)

Any ideas on how to do this via MACRO in Excel? (When the Word document is selected, the top menu reverts to WORD's menu - and SELECTION.WHOLESTORY (Select All via Word's menu) is not supported (?) in Excel.

Any suggestions on a work around would be much appreciated!

Thanks!! :confused:
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Re: Question: Copying data from Word document for PASTE to E

Howdy Merlin, welcome to the board. :)

Try the following. Call the proc from the first proecure, it's setting up an early bind for you, if you don't have the correct reference needed for Excel to work with Word Objects.

Code:
Sub CopWordDoc()
Dim n As Integer, z As Boolean
With ThisWorkbook.VBProject.References
    For n = 1 To .Count
        If InStr(.Item(n).Description, "Microsoft Word") Then GoTo 1
    Next n
    .AddFromGuid "{00020905-0000-0000-C000-000000000046}", 1, 0
    z = True
1: End With
HitIt (z)
End Sub

Private Sub HitIt(z As Boolean)
Dim y As OLEObject, n As Integer
Set y = Sheets(1).OLEObjects(1)
Application.ScreenUpdating = False
y.Verb Verb:=xlPrimary
Set y = Nothing
Documents(1).Content.Copy
Sheets(1).[a1].PasteSpecial xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True

If z = True Then
    With ThisWorkbook.VBProject.References
    For n = 1 To .Count
        If InStr(.Item(n).Description, "Word") Then
            .Remove .Item(n)
            Exit For
        End If
    Next n
    End With
End If

End Sub

It'll remove the reference if it wasn't previously established. My answer is constructed this way as you mentioned distributing your workbook, the Word library reference, while need here, is not established by default.

Hope this helps.
 
Upvote 0

Forum statistics

Threads
1,214,951
Messages
6,122,446
Members
449,083
Latest member
Ava19

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top