export cell contents to Word fields in a protected Word document

**duugg** · 07-06-2009, 06:29 PM

Hello all,

Is it possible to export Excel cell contents to Word fields in a protected Word document?

For example...

What code would be needed to tell Excel to open up, copy and export the contents of A2 in the active sheet of a workbook to "Field 2" in a Word document named "Report 01" and then put the contents of B2 to "Field 2" etc?

Do both applications have to be opened up at the same time or is Excel able to open up Word on its own?

Will the macro be able to....

1. Automatically open up the correct Word document?

2. Look ONLY in a certain folder for the "Report 01" Word document?

or

Bring up a "selection" box that allows you to select the document you wish Excel to export it's data to?

3. Close and save the Word Document without any user intervention?

thanks,

duugg

**T-J** · 07-07-2009, 11:49 AM

The code below covers the basic requirement.

Set a reference to the Word 11.0 object library (Tools, References)

Code:

Sub Export_Data_To_Word_Form()
'set a reference to the Word 11.0 object library (Tools, References)
    Dim appWord As Word.Application
    Dim doc As Word.Document
    Dim strWordDoc As String
    
    strWordDoc = ThisWorkbook.Path & "\Report 01.doc"
    
    Set appWord = New Word.Application
    appWord.Visible = True
    Set doc = appWord.Documents.Open(strWordDoc)
    
    doc.FormFields("Field1").Result = ActiveSheet.Range("A2")
    doc.FormFields("Field2").Result = ActiveSheet.Range("B2")
    
    doc.Close True
    Set doc = Nothing
    appWord.Quit
    Set appWord = Nothing
End Sub

For document selection, look at the GetOpenFilename Method and the FileDialog object, both in VB help with examples.