Send UserForm Data to .DOT Template

[PumaMan]

I have a UserForm in Excel '03 where I am collecting data. I'd like to open a
word template and pass the data on to the template to bookmarks. I'd like to
bypass the process of sending the data to excel cells, then have the data
passed on to the template.

Is that the best way to do it? I assumed it would be faster...but I'm just
starting to learn.

As always, I appreciate the help!

Private Sub BusinessOptionButton_Click()
'Navigate to Business Menu
If Me.BusinessOptionButton.Value = True Then
MultiPage1.BusMainMenu.Visible = True
MultiPage1.Value = 1
MultiPage1.PersMainMenu.Visible = False
Me.PersonalOptionButton.Value = False
End If
End Sub


Private Sub PersonalOptionButton_Click()
'Navigate to Personal Menu
If Me.PersonalOptionButton.Value = True Then
MultiPage1.PersMainMenu.Visible = True
MultiPage1.Value = 0
MultiPage1.BusMainMenu.Visible = False
Me.BusinessOptionButton.Value = False
End If
End Sub
Public Sub UserForm_Activate()

'Sets value to 0 for main page
MultiPage1.Value = 0

' Populates Citizenship Combo Box Data Selection
With CitizenshipComboBox
.AddItem "U.S."
.AddItem "RA"
.AddItem "NRA"
End With
ProcessDocsUserForm.CitizenshipComboBox.ListIndex = 0

' Populates Personal Account Type Combo Box Data Selection
With AccountTypeComboBox
.AddItem "SELECT..."
.AddItem "MYACCESS CHECKING"
.AddItem "REGULAR CHECKING"
.AddItem "INTEREST CHECKING"
.AddItem "CAMPUS EDGE CHECKING"
.AddItem "REGULAR SAVINGS"
.AddItem "UTMA - REGULAR SAVINGS"
.AddItem "MONEY MARKET SAVINGS"
.AddItem "BALANCE REWARDS MONEY MARKET SAVINGS"
End With
ProcessDocsUserForm.AccountTypeComboBox.ListIndex = 0

'Populates State ComboBox Data Selection
With StateComboBox
.AddItem "SELECT STATE..."
.AddItem "ARIZONA"
.AddItem "ARKANSAS"
.AddItem "CONNECTICUT"
.AddItem "DISTRICT OF COLUMBIA"
.AddItem "FLORIDA"
.AddItem "GEORGIA"
.AddItem "ILLINOIS"
.AddItem "INDIANA"
.AddItem "IOWA"
.AddItem "KANSAS"
.AddItem "MAINE"
.AddItem "MARYLAND"
.AddItem "MASSACHUSETTS"
.AddItem "MICHIGAN"
.AddItem "MISSOURI"
.AddItem "NEVADA"
.AddItem "NEW HAMPSHIRE"
.AddItem "NEW JERSEY"
.AddItem "NEW MEXIC"
.AddItem "NEW YORK"
.AddItem "NORTH CAROLINA"
.AddItem "OKLAHOMA"
.AddItem "OREGON"
.AddItem "PENNSYLVANIA"
.AddItem "RHODE ISLAND"
.AddItem "SOUTH CAROLINA"
.AddItem "TENNESSEE"
.AddItem "TEXAS"
.AddItem "VIRGINA"
End With
ProcessDocsUserForm.StateComboBox.ListIndex = 0

End Sub

Private Sub GenerateFormsButton_Click()
' Check for valid data before processing
Call ValidData
If ErrorsBoolean = True Then
Exit Sub
End If

' Begin Data transfer to ?


End Sub


Private Function ValidData() As Boolean
' Check for Valid Data
Dim ErrorsBoolean As Boolean
ErrorsBoolean = False
Dim MessageString As String
MessageString = ""

If AddSignerMaintOptionButton.Value = False _
And RemoveSignerMaintOptionButton.Value = False _
And ChangeTitleMaintOptionButton.Value = False _
And ChangeBeneMaintOptionButton.Value = False Then
ErrorsBoolean = True
MsgBox ("Please select a maintenance option")
Exit Function
End If

If AddressTextBox = "" Then
ErrorsBoolean = True
MsgBox ("Enter a Mailing Address!")
Exit Function
ElseIf CaseNumberTextBox.Value = "" Then
End If

If CaseNumberTextBox.Value = "" Then
ErrorsBoolean = True
MsgBox ("Enter a Case Number!")
Exit Function
End If

If StateComboBox.Value = "SELECT STATE..." Then
ErrorsBoolean = True
MsgBox ("Select a valid State!")
Exit Function
End If

If AcctNumberTextBox.Value = "" Then
ErrorsBoolean = True
MsgBox ("Enter an Account Number")
Exit Function
End If

If Not IsNumeric(AcctNumberTextBox) Then
ErrorsBoolean = True
MsgBox ("Account Number MUST be Numeric!")
Exit Function
End If

If AccountTypeComboBox.Value = "SELECT..." Then
ErrorsBoolean = True
MsgBox ("Select a valid Account Type!")
Exit Function
End If

If TitleLine1TextBox.Value = "" Then
ErrorsBoolean = True
MsgBox ("Title Line 1 cannot be empty!")
Exit Function
End If

If TitleLine2TextBox.Value = "" _
And TitleLine3TextBox.Value <> "" Then
ErrorsBoolean = True
MsgBox ("Line 2 should not be empty if Line 3 has data!")
Exit Function
End If
End Function

Private Sub ClearButton_Click()
'Clears the form
If AddSignerMaintOptionButton.Value = True Then
AddSignerMaintOptionButton.Value = False
ElseIf RemoveSignerMaintOptionButton.Value = True Then
RemoveSignerMaintOptionButton.Value = False
ElseIf ChangeTitleMaintOptionButton.Value = True Then
ChangeTitleMaintOptionButton.Value = False
ElseIf ChangeBeneMaintOptionButton.Value = True Then
ChangeBeneMaintOptionButton.Value = False
End If

AddressTextBox.Value = ""
CaseNumberTextBox.Value = ""
ProcessDocsUserForm.CitizenshipComboBox.ListIndex = 0
ProcessDocsUserForm.StateComboBox.ListIndex = 0
ProcessDocsUserForm.AccountTypeComboBox.ListIndex = 0
AcctNumberTextBox.Value = ""
TitleLine1TextBox.Value = ""
TitleLine2TextBox.Value = ""
TitleLine3TextBox.Value = ""
End Sub

Private Sub ExitButton_Click()
'Exit Program
Unload Me
End Sub

 

[ryguy7272]

This is interesting; I never thought of doing it from an Excel UserForm
DIRECTLY to Word. It is probably possible, but you can probably save
yourself a lot of headaches if you send it from the Excel sheet to the Word
doc. So, go from the UserForm to the Sheet, like this.
Private Sub CommandButton1_Click()
On Error Resume Next
Sheets("Sheet1").Activate
Cells(1, 1) = TextBox1.Text
Cells(2, 1) = TextBox2.Text
Cells(3, 1) = TextBox3.Text
'(1,1) is Cell A1, (2,1) is Cell A2, etc.
On Error Resume Next
End Sub


Now, go from the Excel sheet to the Word doc., like this:
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)
objWord.activedocument.variables("FirstName").Value = Range("FirstName").Value
objWord.activedocument.variables("LastName").Value = Range("LastName").Value
ActiveDocument.Fields.Update
objWord.Visible = True
End Sub

In Word, you would name one DocVariable "FirstName" and in Excel you would
name the corresponding cell "FirstName" (this is a Named Range).

You have to insert DocVariables in Word. If you don't know how to do that,
Google around for it (I'm at achool now and they have 2007 here; I don't
really like 2007 and I really have no idea how DocVariables work in Word
2007).

That's pretty much it. Oh, also, set a reference in Excel to Word and run
everything from Excel.


Good luck,
Ryan---

 

[Doug Robbins - Word MVP on news.microsoft.com]

Set a reference to the Word Object Model under Tools>References in the VBE
and then use the following, which assumes that you have located docvariable
fields in the template at the locations where you want the data to appear.

Dim objWord As Word.Application
Dim wrdDoc As Word.Document
Dim WordWasNotRunning As Boolean
On Error Resume Next

Set objWord = GetObject(, "Word.Application")
If Err Then
Set objWord = New Word.Application
WordWasNotRunning = True
End If
objWord.Visible = True
objWord.Activate
Set wrdDoc = objWord.Documents.Add("TemplateName")
With wrdDoc
.Variables("varname").Value = somecontrol.Text
'etc
.Range.Fields.Update
End With

If WordWasNotRunning Then
oWord.Quit
End If

Set wrdDoc = Nothing
Set objWord = Nothing


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com