Create a Range from Excel

[Andy]

I am trying to write some data and copy some cells from Excel to Word from
Excel.

In Excel VBA, I am trying to open a new Word application and then begin to
write the data.

I seem to be falling at an early fence with this code :-

Dim objWord As Word.Application
Dim WSD As Worksheet
Dim objDoc As Word.Document
Dim wdRn As Word.Range
Dim PreMess as string

Set WSD = ActiveSheet
Set objWord = New Word.Application
Set objDoc = objWord.NewDocument

PreMess = "Hello"

Set wdRn = objDoc.Range(0, 0)

With wdRn
.InsertParagraph
.InsertBefore PreMess
End With

etc.

The code fails at the line of code - Set objDoc = objWord.NewDocument
The error is 'Type mismatch' - error 13

So I am successfully creating the application but not a new document within
the application.

I guess you can see what I am trying to do - can anyone help?

At some point I will also need to save the document and close the Word
application but struggling to find this using F2.

Thanks.

 

[Tony Jollans]

Set objDoc = objWord.Documents.Add

To save ...

objDoc.SaveAs FileName:=whatever

To close the document

objDoc.Close

To close Word

objWord.Quit

 

[Andy]

Thanks Tony - I really appreciate your help.

That worked fine.

One last thing that you might be able to help with.

Rather than creating a new document I have opened a Word template with :-

objWord.Documents.Open "C:\Form.dot"

The form has several Form Fields. I need to access some of these form fields
and then write some text (still from Excel).

I tried :-

Set wdRn = objDoc.FormFields(2).Range
With wdRn
.InsertAfter "fred"
End With

That worked but 'fred' appeared after the Form field. I need the field
itself to be overtyped with 'fred'

I wondered about using bookmarks possibly. I also tried using Select such as
:-

objDoc.FormFields(2).Select

Selection.TypeText Text:="fred"

This method generates an error :-

'Object does not support Property or Method'

Can you please advise?

Thanks again.

 

[Tony Jollans]

Well, you shouldn't really use templates like that; the idea is that you
create new documents based on them.

Regardless of that, if FormFields have names, those names *are* bookmarks.
To assign some text to a formfield itself you should use the Result Range,
so

objDoc.FormFields(2).Result + "Your text here"

(if your text is longer than 255 characters see
http://word.mvps.org/faqs/MacrosVBA/SetLongFmFldResult.htm)

 

[Andy]

Brilliant - Thanks again Tony.

I have also taken your advice about incorrect use of template. I have
changed :-

objWord.Documents.Open "C:\Form.dot"

to :-

objWord.Documents.Add Template:="C:\Form.dot",
DocumentType:=wdNewBlankDocument