Excel Spreadsheet to Word UserForm

[JamesDekker]

Hi Folks,

This is my first post here and I hope you can help me. I am working on a project in excel 2003 to copy data from an excel spreadsheet to a existing word template userform.

The spreadsheet is a calculator that is used to calculate an account balance and the template is a letter that is sent to the customer with the account balance information on it. The calculator is only numbers and dates and the template is all the customer info (name, address ect.).

As the word template already exists and is in use I want to programme my excel calculator to copy the data that is entered and calculated in the spreadsheet to the word template userform.

I am new to VBA and I have got as far as opening the template from the calculator........
------------------------------------------------------------------
Sub LaunchWrdTemplate()
Dim WrdApp As Object
Dim WrdDoc As Object


Set WrdApp = CreateObject("Word.Application")
WrdApp.Visible = True
Set WrdDoc = WrdApp.Documents.Add("Filepath\Word.dot")
End Sub
------------------------------------------------------------------
I now need to take the data from the spreadsheet and populate the word userform. Is there anyway to simply copy a cell contents from the spreadsheet to a specified field in a word userform using VBA in excel??

I have tried a few ways and I am going a bit crazy ( like this guy) so it be great if someone could help me out. Thanks.

[Bob Phillips]

Use Word Bookmarks.

Insert bookmarks into your template, then it is simple to laod data directly into them from Excel, like so

[vba]

WrdDoc.Bookmarks("Balance").Range .Text = ActiveWorkbook.Worksheets("Sheet1").Range("H5").Value
[/vba]

[JamesDekker]

I did think about this but because the word template is already in use I need to populate the existing fields on the form. The excel spreadsheet calculator only fills in about half of the data required to complete the letter. If I skipped the form and set the bookmarks directly the user would not know what fields have been completed, also word form has validations built in that require the form fields to be filled in by the user.

Thanks for the advice and I will give it a shot and see how it goes.

[Kenneth Hobs]

If you mean formfields.

[VBA]'http://www.mrexcel.com/forum/showthread.php?t=333200
Sub FillForm()
Dim wdApp As Object, WD As Object, rn As Long
rn = ActiveCell.Row
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set WD = wdApp.Documents.Open(ThisWorkbook.Path & "\Car Information Page.doc")

wdApp.Visible = True
With WD
.FormFields("Brand").Result = Cells(rn, "B")
.FormFields("Model").Result = Cells(rn, "C")
.FormFields("Chasis").Result = Cells(rn, "D")
.FormFields("Engine").Result = Cells(rn, "E")
.FormFields("Color").Result = Cells(rn, "F")
.FormFields("YearMonth").Result = Cells(rn, "G").Value & "/" & Cells(rn, "H").Value
End With

Set WD = Nothing
Set wdApp = Nothing
End Sub[/VBA]