Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Poking Excel data into a Word Documnet
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?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Poking Excel data into a Word Documnet
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 "CW" wrote in message ... 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?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy excel data (inc. textbox data) into Word | Excel Discussion (Misc queries) | |||
Transfer Excel data into Word, including text box data | Excel Discussion (Misc queries) | |||
I want to convert word column data to excel row data to sort addre | Excel Discussion (Misc queries) | |||
Need help getting data from Word to Excel | Excel Discussion (Misc queries) | |||
Print labels by using Excel data in a Word mail into word | Excel Discussion (Misc queries) |