Paste/Copy Range of Cells from Excel to a Bookmark in Word using WORD VBA

Question

I am looking at inserting/pasting a range of text data (40 columns) from Excel into bookmarks in Word. Most of the answers are done using Excel VBA, which is so not practical for my use case as I will have the Word document open, add a button that would run this 'import data' macro. I actually already have a button in the doc that inserts images into bookmarks, so that's one more reason I don't want to do it via Excel VBA.

I know this is not great code, but for the lack of definite leads, I'm throwing it here and hope that this gives you an idea of what I'm trying to achieve:

Sub ImportData()

Workbooks.Open ("\Book2.xlsm")
    ActiveWindow.WindowState = xlMinimized
    ThisWorkbook.Activate

    Windows("Book2.xlsm").Activate
    Range("A1:AF1").Select
    Selection.Copy
    Documents("test.docm").Activate
    Selection.GoTo What:=wdGoToBookmark, Name:="Overlay_1"
    Selection.Paste

End Sub

PS: It would be great if I could sort of 'transpose' the 40 columns into rows as it is pasted in Word.

Here's an update to my code based off @Variatus 's advice:

Sub ImportData()

Dim wb As Workbooks
Dim ws As Worksheets
Dim objSheet As Object
Dim objWord As Object
Set objWord = CreateObject("Word.Application")

wb.Open ("C:\Users\pc\Documents\Book2.xlsm")
Set objSheet = CreateObject("Excel.Application")
ActiveWindow.WindowState = xlMinimized
Set ws = Workbooks("Book2.xlsm").Sheets("Sheet1")
ws.Range("A1").Value.Copy

With objWord.ActiveDocument
    .Bookmarks("Bookmark_1").Range.Text = ws.Range("A1").Value
End With

End Sub

I'm getting this error:

Runtime Error '91': Object variable or With block variable not set.

Notice how I stuck with a single cell reference for now (A1). I'll just update my code as I learn along the way :)

I have removed the inappropriate [word] tag (that tag has nothing to do with MS Word) and added an [excel-vba] tag. The solution to your problem is going to require both Word and Excel VBA to be used, just as in all the other answers you looked at in the last 2 days - the only difference between the answer you want and the answers you have already seen will be which Application object is created prior to your code starting, and which Application object is created as part of your code. (In your case, a Word.Application will already exist, and you will create an Excel.Application.) — YowE3K

Variatus Variatus · Accepted Answer · 2017-12-10T01:35:13

When you click the button in your Word document you want the following sequence to be initiated.

Create an Excel application object. Make sure that a reference to Excel has been set (VBE > Tools > References) so that Excel's VBA objects are available.
Using the Excel application object, open the workbook. Create an object. Place the object in an invisible window.
Definitely forget about activating or selecting anything in either the workbook or your Word document. The latter is active and remains active from beginning to end. The bookmarks are points in your document you can reference and manipulate by name without selecting them. The Excel workbook is invisible. You can access any part of it using the Range object.
The data you want from your workbook are contained in Worksheets. Be sure to create an object for the worksheet you are about to draw data from.
Excel tables don't translate very well into Word tables. If you do want to go that way I suggest that you use VBA to create the table you want in Excel (transpose the data before you import them into Word). However, you may find it easier to first create the tables you want in Word and then just copy values from your Excel source into the word tables. That would involve taking one cell value at a time and placing it into one Word table cell. Transposing would be done by the algorithm you employ.
Close the workbook. Quit the Excel application. Set the Excel application = Nothing. At the end of your macro everything is as it was before except that your document has data in it which it didn't have before.

Each of the above six points will lead you to at least one question which you can ask here after you have googled the subject and written some code. In fact, I strongly urge you to create one Main procedure (the one which responds to your button click) and let that procedure call various subs which carry out the individual tasks and functions to support the subs. The smaller the parts you create the easier it is to write the code, to find questions to ask and get answers to them. If you plan your project well expect to have about 12 procedures in it by the time you are done. Good luck!

Paste/Copy Range of Cells from Excel to a Bookmark in Word using WORD VBA

1 Answers