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!

 

[shknbk2]

An Excel function to copy to the clipboard from HERE is:

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

This can set the text in the clipboard or return its contents.

You could use it with your method for accessing the selected text like:

Clipboard wdApp.Selection.Text

 

[WJK-4]

shknbk2: Thank you for your thoughts on this. I understand that the function you posted does the task of copying to the clipboard but I'm not too sure what you mean about using it with my method for accessing the selected text. Starting in Excel, how exactly do you suggest to have this function 'copy to clipboard' (of the text from the Word doc which is an object on the Excel worksheet) work within the Excel macro? Is that what you had in mind or is it something else? Thanks again for your thoughts.​

 

[shknbk2]

I'm not sure how you are selecting the text you want to copy to the clipboard. In my example below, it selects all text in the document, but if you need to automate selecting only a portion of that text, the details of that will have to be known. After the Sub is run, the entire document text is found in the clipboard.

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
    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]

Okay, thanks again, that is working for me which is great and the comments you show are quite helpful! Can you please explain a little more about how the sub routine interacts with the function....in other words, does the sub run first and then the function next? Also, I was mistaken in my assumption about the text in the object remaining highlighted ....which I thought would stay that way when saved but I learned that when the workbook is closed and then reopened, the highlighted text is no longer highlighted....my mistake! Actually, what I need is all the text in the document except for the first three lines. Is that an easy adjustment to the code, or if not, maybe I can figure out how to put that in a header on the doc or even off the doc on the Excel sheet. One other issue that came to mind is....I have many individual worksheets in this workbook with one embedded doc on each worksheet so do I need to put this 'copy to clipboard' code on each individual worksheet or can it be stored in the workbook and invoked from any individual worksheet to copy the doc on that particular worksheet? In any event, I appreciate all your insights and I'll keep fine tuning this workbook....it coming together now!

 

[shknbk2]

For selecting all but the first 3 lines, you can use:

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 wdLine, 3
    Clipboard objWord.Application.Selection.Text
    
    objDoc.Close 'Closes document but not the Word Application
    Range("A1").Select
End Sub

How are you planning on calling this code on each Sheet? A button on the page? Double-clicking a certain cell? You could even create a button to put at the top in the Ribbon. All of these are possible, but it just needs to be decided how you would like it to work.

You should be able to call this from any sheet. As long as that sheet has an embedded Word doc named Object 1, it should run.

For Sub vs. Function, lots of advice on the Internet.
For example,

Difference Between Subroutine and Function in Excel VBA

Function has some important differences with subroutine. Go through the article to know the differences and understand them properly.

www.exceldemy.com

 

[WJK-4]

Thank you for this help, I've been working on trying to get this to run and it's not quite executing properly. When I run it, the result is that the Word is launched (outside of Excel) and the doc opens and the entire doc is highlighted but then it stops. I'm getting a runtime 4120 error that says "bad parameter." The debug button leads to the line objWord.Application.Selection.MoveStart wdLine, 3 as the one it seems to be having a problem with.

I will keep working on it and if you have any further suggestions, feel free! Thank you,,,

 

[shknbk2]

I shouldn't have used the Word constant "wdLine" (at least by itself) since Excel doesn't know what that is. Change that line to this and see if it fixes the code.

objWord.Application.Selection.MoveStart 5, 3

 

[WJK-4]

Okay, I tried that and it works like a charm! Thank you!

I've been working with this on two different machines with two different versions of Excel. It works fine on the older machine (Windows XP & Excel 2007) but not on the newer one. However, the machine that I need to have this work on is the newer one (Windows 10 ....64 bit...Excel is part of Office 365 ProPlus).

The only issue (that I know of) seems to be that the code is working fine but it is NOT copying the text of the Word object to the clipboard using Excel in Office 356 ProPlus. As I said, in Excel 2007 on the old machine, it seems to work fine, copying all text (starting at line 4......as is should) to the clipboard which can be pasted wherever needed. It's just not copying to the clipboard in the version of Excel in Office 356 ProPlus for some reason.

Any thoughts on what's causing that little hiccup? This is so close to working if we can just solve that one issue. Thanks!

 

[shknbk2]

I don't have ProPlus, but I've got Excel 365, and it works. The only think I could think to do would be to step through the code to try to find out why the clipboard copying isn't working.