Transfer Excel data into Word forms

[Kaylen]

I have an Excel form and a Word form that need to go together. The Excel form
has to be in excel format because of complicated calculations. After the
Excel form was filled out, currently the users have to take some of the
results from the Excel form and duplicate them into a letter that is in Word
format. Both the Excel form and letter were later sent out together. Is there
a way for have this programmed to eliminated having to cut and paste data
from Excel to Word? Maybe a macro in Excel that allows the generation of the
letter.doc after done with the Excel form? Or the other way around? I don't
know. I want to make this process short and simple as possibles because the
users of the forms are not computer savy. Any help is appreciated.

 

[Joel]

YOu can control word documents through excel. Here is some starter code


Sub Test()

'
FName = "c:\temp\abc.doc"
WordWasRunning = True

On Error Resume Next
'Get word object if application already is opened.
Set WDApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set WDApp = CreateObject("Word.Application")
WordWasRunning = False
End If

WDApp.Visible = True 'at least for testing!

Set WDDoc = WDApp.documents.Open(Filename:=FName)

Set WordTable = WDDoc.tables(1)
WDDoc.Close

End Sub

 

[Carlos]

Hi,

Perhaps it would be better for Kaylen to explore mail-merge first and, if it
does not meet its requirements, turn to OLE automation. Interaction between
Word and Excel is not hard to accomplish, but it requires you to know well
Word and Excel objects.

Kaylen, if you wish, I can post some sample code to transfer data from an
Excel table (a range) to a Word table. Having said that, I think one must
exhaust simple solutions (mail-merge) before moving to more complex ones (OLE
automation).

 

[Kaylen]

Thank you Carlos for your insights. I am not a pro with programming, actually
I'm a beginner trying to learn. I have thought of mail merge but that is a
little too complicate for the users to do for they are very limited with
computer knowledge. My idea is to have a command button somewhere in Excel
where the users can just click on after entering all the data in Excel and
all of the relevant infomation( info in certain cells only) is duplicated or
merged into the letter that is in Word format. After the merge, the completed
letter is then open for the users to see and double check or do any final
edits. If possible, can you post me a sample OLE automation code that I can
try to use? And where would I put this code in Excel? I'm pretty new with
this therefore the more instructions the better.

THank you so much for your help!

 

[Carlos]

Kaylen:

Here is the code. For the code to run, you must have a folder named "C:\OLE
Automation\" containing an Excel workbook named "Excel.xls" and a Word
document named "Word.doc". Excel.xls contains data in the range "A1:E11" and
Word.doc must be empty. "data" is the name of the worksheet were the data is.
The code will look messy in the chat, so copy and paste it into a Visual
Basic module IN Excel.xls.

Option Explicit
Public Sub TransferData()
'This macro transfers the data range "A1:E11" to a table in Word
'
'Constants:
'docFullName = The full name of an already existing Word document
'
'Variables:
'doc = The Word document (assumed to be empty)
'i = A counter (for rows)
'j = A counter (for columns)
'tbl = A Word table
'wdRng = A Word range (the first paragraph of doc)
'wks = The worksheet "data" that contains the data range
'
'Const docFullName = "C:\OLE Automation\Word.doc" 'Only if you want a
specific document
Dim doc As Object
Dim i As Long
Dim j As Long
Dim tbl As Object
Dim wdApp As Object 'Only if you require a new document each time
Dim wdRng As Object
Dim wks As Worksheet

'Assing Word objects 'Only if you require a new document each time
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
Set doc = wdApp.Documents.Add

'Assign variables and objects
'Set doc = GetObject(docFullName) 'Only if you want a specific document
Set wdRng = doc.Paragraphs(1).Range
Set tbl = doc.Tables.Add(wdRng, 11, 5)
Set wks = ThisWorkbook.Worksheets("data")

'Transfer the data
With tbl
For i = 1 To 11
For j = 1 To 5
.Cell(i, j) = wks.Cells(i, j)
Next j
Next i
End With

'Save and close doc 'Only if you want a specific document
'Call doc.Save
'Call doc.Close(False)

'Clean
Set doc = Nothing
Set wks = Nothing

End Sub

 

[ryguy7272]

Take a look at this:
http://word.mvps.org/FAQs/InterDev/ControlWordFromXL.htm

This too:
http://addbalance.com/usersguide/fields.htm

And this:
http://gregmaxey.mvps.org/Word_Fields.htm

Finally, once you get the DocVariable fields set up in Word (hit Alt + F9 to
see all fields), run this code from Excel.
Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)

With doc
..Variables("VarNumber1").Value = Range("VarNumber1").Value
..Variables("VarNumber2").Value = Range("VarNumber2").Value
'etc
..Range.Fields.Update
End With

'ActiveDocument.Fields.Update

objWord.Visible = True

End Sub

Note: This code runs in Excel; pushes Excel variables (assigned as Named
Ranges) to Word.

Regards,
Ryan--