Type mismatch on field result statement sending from Excel

[Cory]

The situation:
Using Office XP
I have one Excel document and one Word document template in this operation.
The concept of operation is that Excel will create a word document off of
the template, and fill in several fields that are on the resulting document
with values from the worksheet. Everything works fine until I try to
actually set data into the fields.

Even when in the immediate window in Word, I can make a statment like:
ThisDocument.Fields("Text1").Result.Text = "Something" and it returns Type
mismatch.

I have tried setting the variables to Variants so that they can be whatever
it needs, setting them as ranges and using the .result property, setting as
strings, etc. Nothing works.

I know that it is supposed to work because I have done it previously. I
just can't remember what I did differently... Any thoughts?

Cory

 

[Helmut Weber]

Hi Cory,

you don't mean something like

ActiveDocument.FormFields("Text1").Result = "Something"

by any chance?

I'm not so sure about the use of ThisDocument.
I got a feeling, that in this case the
macro should be placed in the actual document.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Jay Freedman]

Helmut is correct that you should be using ActiveDocument (or preferably a
variable of type Document to which you assign the result of the
Word.Documents.Add method) instead of ThisDocument.

But that won't stop the type mismatch error. The cause of the error is this:
When a macro is running in Word, you can identify a field by its numeric
index or by its bookmark name -- ActiveDocument.Fields(1) or
ActiveDocument.Fields("Text1"). When you call into the Word object model
from Excel, it will only accept the numeric index. The type mismatch occurs
when the name (a string) is passed but a number is expected.

To work around this, use the Bookmarks collection and select the first (and
only) field in the range of the named bookmark:

oDoc.Bookmarks("Text1").Range.Fields(1).Result.Text = "Something"

An alternative would be to write the macro in the Word template as an
AutoNew macro, and have Excel just create the new document. The document can
then populate its fields from the open worksheet.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.