Excel Macro combined with a Word macro

[WJK-4]

I am attempting to write a macro on an excel worksheet that has an embedded Word object. I would like the Excel macro to open the Word doc, copy highlighted text in the Word doc to the Office clipboard and then return back to cell A1 in the Excel worksheet. I believe this involves combining the Word VBA with the Excel VBA but I'm not sure how to make that work.

For the Excel macro, starting in cell A1, it's just a double click on the Word object to open it for editing and then going back to cell A1 on the Excel worksheet.

Sub Macro1()
ActiveSheet.Shapes.Range(Array("Object 1")).Select
Selection.Verb Verb:=x1Primary
Range ("A1").Select
End Sub

The macro in Word to simply copy text that was highlighted is:

Sub Macro1()
Selection.Copy
End Sub

The question is, how do I insert or combine the Word macro within the Excel macro so I can run the macro on the Excel worksheet and end up with the text copied from the Word doc to the Office clipboard for use to paste anywhere I need to put it with a simple "Ctrl V?"

I can't imagine that this combination is too difficult but I certainly do not how to do it. Any thoughts would be much appreciated Thanks!

 

[WJK-4]

I have been trying to troubleshoot what's going on with why the clipboard copying is not working.

First, I looked carefully at how this code executes on my old machine where it seems to work fine, except for an error message that's coming from Word.

On the old machine, the following error message from Word pops up twice when running the code and if you just click OK twice the code finishes executing and the correct text is copied to the clipboard. I don't know where the error comes from but other than that .......No problem!

On the new machine (with Office 365 -Excel is Excel for Microsoft 365 MSO (16.0.12730.20188) 64-bit) when you run the code, the first thing you see is a
Microsoft VBA box that says "Complie Error: Syntax error and it's on that line we substituted to start the copying after the first 3 lines of text on the Word doc (objWord.Application.Selection.MoveStart 5, 3)

If you delete that line, the entire document is selected as you would expect and the codes executes to the end but there is nothing on the clipboard

When stepping through the lines, I noticed in the local window when you click to expand "objDoc", under "Expression" you see "HTMLProject" and under "Value" it says: <This command is not available on this platform> and under "Type" it says "HTML Project" I am certainly no expert when it comes to debugging but I guess that could possibly be causing an issue........maybe, maybe not. Anyway, I'm not sure what's next here but it's frustrating to get all the way to a point where if you step through the code to the exact spot where it's supposed to copy to the clipboard and you manually hit "Ctrl-C" you have what you want on the clipboard........but, of course, that's what the code's supposed to do.

 

[shknbk2]

When I set up my Excel to try this project out, I created a current Word version document (i.e., docx) and put it into Sheet1. My objDoc doesn't have an "Expression" or "Value" variable. It does have a "Type" and it is wdTypeDocument. Is there any chance you can save the embedded file as a Word 365 document and re-embed it in the sheet?

I found some info on the web addressing "cannot start the converter", and it seemed to point to users trying to open .doc files. Maybe that would also go away if you can re-save as docx.

 

[WJK-4]

Well, I've saved a doc as a docx and that didn't seem to help.

When I originally put the document on the Excel sheet, I just used.......Insert......then Text Object and then copied and pasted text into the new text object. Is that how you did it? In the local box when debugging, I see under "Expression" one of the lines is "objDoc" and mine has no value and then just says "Object" in the Type column. Is that where yours says 'wdTypeDocument" ?

Also, when I right click on the embedded doc, in the formula bar reads =EMBED("Word.Document.MacroEnabled.12","") Is that what you see there?

I wish there was a way to get your Excel sheet so I could directly compare. One other question.....when you click on Tools when you have the VBA window open, what references do you have enabled? Maybe that could be the problem! Who knows?? Thanks again for your comments.

 

[shknbk2]

I had first created a Word document with sample text saved on my desktop. Then, I inserted the document using Insert -> Text -> Object. I chose the "Create from File" tab at the top and also checked the "Display as icon" checkbox. Doing this gave me a different EMBED result:

However, I see how to match your settings, and I'll go through step-by-step:
1. On a brand new workbook, I selected Insert -> Text -> Object and chose MS Word Macro-Enabled Document

2. In the document, I typed "=rand(8,10)" and hit Enter. This is a (maybe not so) secret of Word that generates 8 paragraphs of 10 sentences. You can put whatever numbers you want for this formula. I just don't have other text to put in as filler. The resulting text is fixed (it's not random text but comes from a fixed set of text from somewhere), and if you run out of preprogrammed text because your numbers are high, it repeats.

3. This expands the text object down a ways and occupies (after I moved it a little) cells A3:J45-ish.

4. When I select the object, it gives me the EMBED formula you have:

5. I open the VBE and copy the code from the previous posts, combining all 3 (I put the code again at the bottom of this post).

6. I haven't added any new references, and these 4 are the default ones selected when I open my VBE:

7. I run the code and can paste the copied text beginning at the third line also shown above starting with "document look professionally..." It's all there.

8. My Locals objects are as follows, which I think might be similar to yours:

When I was talking about the Type before, I mistakenly referred to the Type variable you get when you expand objDoc, not the one in the third column above. So I wasn't referring to the same thing you were, which was my bad.

These are all the complete steps I just now performed, and the copying to clipboard works fine. There has to be an underlying issue we don't understand yet if you still don't get the same results by following these steps.

Sub OpenWordCopyToClipboard()
    Dim objWord As Object  'Word Object
    Dim objDoc As Object  'Word.Document
    Dim sh As Shape
    Dim objOLE As OLEObject
    
    Set sh = ActiveSheet.Shapes("Object 1")
    Set objOLE = sh.OLEFormat.Object
    objOLE.Verb xlOpen
    Set objWord = objOLE.Object
    Set objDoc = objWord.Application.Activedocument
    
    'Example to select all text in document and copy to clipboard
    objDoc.Range.Select
    objWord.Application.Selection.MoveStart 5, 3
    Clipboard objWord.Application.Selection.Text
    
    objDoc.Close 'Closes document but not the Word Application
    Range("A1").Select
End Sub

Function Clipboard$(Optional s$)
    Dim v: v = s  'Cast to variant for 64-bit VBA support
    With CreateObject("htmlfile")
        With .parentWindow.clipboardData
            Select Case True
                Case Len(s): .setData "text", v
                Case Else:   Clipboard = .GetData("text")
            End Select
        End With
    End With
End Function

 

[WJK-4]

I have to apologize for not replying sooner and I really appreciate all the time and detailed work that was provided to clarify the solution to this question. Using these comments, I tried to recreate the steps to make this work on my newer machine at work and I just could not get it to cooperate. I have no doubt whatsoever that these steps do in fact solve the problem. I have come to the conclusion that the configuration of the security settings on my machine at work are somehow preventing the macro from copying to the clipboard. For that reason, I see no way now to implement the code to work the way we want it to on that computer. I may be wrong about that but I have no other explanation after trying for hours to make this work. In any event, I applaud your efforts to shine the light on this and I did learn a few things about VBA that I did not know before (which was not much to begin with....LOL !!). Thank you very much for your efforts, much appreciated my friend!

 

[shknbk2]

You're welcome. Sorry it couldn't work out.