Poking Excel data into a Word Documnet

[CW]

I am trying to write a program in Excel that will work
with a Word document to create a letter with various
variables calculated in the excel workbook. I tried using
the DDE Initiate and Poke commands(I found these in a box
by John Walkenback - excel for win 95 power programing
with VBA) but it doesn't seem to work. Code I'm using is
below. Everytime I run it I first get a message box
saying "remote data not accessible, start application
WinWord.exe" I choose yes then get an error code 13, type
mismatch and the progam stops at the DDEInitiate line.


Sub Macro2()
'
' Macro2 Macro
' Macro recorded 11/13/2003 by James
'

IName = Sheets("sheet1").Range("b1")
ITitle = Sheets("sheet1").Range("b2")
Borrower = Sheets("sheet1").Range("b3")
Baddress = Sheets("sheet1").Range("b4")
BankName = Sheets("sheet1").Range("b5")
Sal = Sheets("sheet1").Range("b6")
BkAbrv = Sheets("sheet1").Range("b7")
BAbrv = Sheets("sheet1").Range("b8")
LoanType = Sheets("sheet1").Range("b9")
LoanAmt = Sheets("sheet1").Range("b10")

cfile = Application.GetSaveAsFilename & ".doc"
channel1 = DDEInitiate("WinWord.exe", cfile)

DDEPoke channel1, "IName", IName
DDEPoke channel1, "ITitle", ITitle
DDEPoke channel1, "Borrower", Borrower
DDEPoke channel1, "BAddress", Baddress
DDEPoke channel1, "BankName", BankName
DDEPoke channel1, "Sal", Sal
DDEPoke channel1, "BkAbrv", BkAbrv
DDEPoke channel1, "BAbrv", BAbrv

DDETerminate channel1

Application.ActivateMicrosoftApp xlMicrosoftWord


End Sub


It may be that I am running Office / excel 2000 but it
seems like there should be some way to pass information
from excel to word.

Can anyone help me??

 

[Henry]

CW,

Write your Word doc with formfields in the places you want to insert data.
(Don't use mergefields).
Set the bookmarks for each of these fields with a recognisable name.
Save your Word Doc in the same folder as your XL program is.
(Not necessary, but it's easier that way)

In XL
Private Sub MyDocPrint()

Dim appWD As Word.Application
'Set-up appWD
Set appWD = CreateObject("Word.Application.9") 'Open
M.S. Word
appWD.Visible = False
'Hide word window (if you want)
appWD.ChangeFileOpenDirectory ActiveWorkbook.Path 'Word looks here
for file
appWD.Documents.Open Filename:="YourDocNameHere", ConfirmConversions:=False,
ReadOnly:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="",
Revert:=False, _
WritePasswordDocument:="", WritePasswordTemplate:="",
Format:=wdOpenFormatAuto 'Open doc
With ActiveSheet
appWD.ActiveDocument.FormFields("Title").Select
'Title
appWD.ActiveDocument.FormFields("Title").Result = .Cells(3,
2).Value

appWD.ActiveDocument.FormFields("Title2").Select
'Title
appWD.ActiveDocument.FormFields("Title2").Result = .Cells(3,
2).Value

appWD.ActiveDocument.FormFields("Initial").Select
'Initial
appWD.ActiveDocument.FormFields("Initial").Result = .Cells(3,
3).Value

appWD.ActiveDocument.FormFields("Surname").Select
'Surname
appWD.ActiveDocument.FormFields("Surname").Result = .Cells(3,
4).Value

appWD.ActiveDocument.FormFields("Surname2").Select
'Surname
appWD.ActiveDocument.FormFields("Surname2").Result = .Cells(3,
4).Value

appWD.ActiveDocument.FormFields("number").Select 'House/flat
number
appWD.ActiveDocument.FormFields("number").Result = .Cells(3,
5).Value

appWD.ActiveDocument.FormFields("Address1").Select 'First
line of address
appWD.ActiveDocument.FormFields("Address1").Result = .Cells(3,
6).Value

'you get the idea.................................
'etc., etc.......................

End With
DoEvents
appWD.PrintOut 'Print
letter
Application.Wait Time + TimeValue("00:00:05") '5 seconds delay to
let printing finish
appWD.DisplayAlerts = wdAlertsNone 'Turn off alerts
appWD.Quit SaveChanges:=False
End Sub

HTH
Henry