Inserting text in a embedded Word template, at a bookmark, from excel. “error 13 Type mismatch” error

Question

I am currently working on a project that have one word template embedded in one excel template.

A button in excel was created for opening the embedded word template, exporting the data from excel and puting them into word by using bookmarks in word template. The issue is that word report can be generated only once, because the text will be insert into the original bookmark rather than overwrite the previous data.

I'm trying to export a named field from excel(CoverPageRCA) and copy it into an embedded word template using a bookmark (bkmtable1_1).

I get:

run-time error 13 Type mismatch

that occurs at the following line:

Set bkMark = ActiveDocument.Bookmarks(bookmarkname).Range

I searched the web and spent almost 24 hrs on it. Can anybody please suggest a solution?

Option Explicit

Dim WD As New Word.Application

Dim RCAcell1 As Range

Sub CreateRCAReports1()

Dim wordDoc As Word.Document

Dim oleObj As oleObject

Dim WordApp As Word.Application

WD.Visible = True

Set oleObj = ActiveWorkbook.Sheets("CoverPageRCA").OLEObjects(1) oleObj.Verb xlVerbPrimary

Set WordApp = oleObj.Object.Application

With WordApp

 .Visible = True

 .Activate

 Set wordDoc = .Documents(1)

 End With

'-------------------------------------------------------

ThisWorkbook.Sheets("CoverPageRCA").Activate

ActiveSheet.Range("B2").Select

Set RCAcell1 = ActiveSheet.Range(ActiveCell, ActiveCell.End(xlDown))

'go to each bookmark and type in details

CopyCell1 "bkmtable1_1", 1

Set WD = Nothing

End Sub

'----------------------------------------------------------

Sub CopyCell1(bookmarkname As String, RowOffset As Integer)

Dim bkMark As Range

'clear content on each bookmark and add new bookmarK

Set bkMark = ActiveDocument.Bookmarks(bookmarkname).Range

bkMark.Select

bkMark.Text = "dsfsf"

ActiveDocument.Bookmarks.Add bookmarkname, bkMark

'copy each cell to relevant Word bookmark

WD.Selection.GoTo What:=wdGoToBookmark, Name:=bookmarkname

WD.Selection.TypeText RCAcell1(RowOffset, 1).Value

End Sub

Hi Jonathan, welcome to SO. If nobody can help, consider posting larger sample of your code, and even better an MCVE. Good luck. — J. Chomel

GCSDC GCSDC · Accepted Answer · 2016-07-06T05:59:10

Looking at the code, the issue is on declaration of bkMark:

Dim bkMark As Range

The range object exists on both Excel and Word (different objects), and as the code above runs on excel, it will declare bkMark as an Excel Range object, not a Word Range object.

But the range returned on the line below is a Word range, causing the type mismatch error.:

Set bkMark = ActiveDocument.Bookmarks(bookmarkname).Range

To fix this issue, you must declare bkMark as a Word range,:

Dim bkMark As Word.Range

Inserting text in a embedded Word template, at a bookmark, from excel. “error 13 Type mismatch” error

1 Answers