merge current form data to a new word file based on a word template

[DeZZar]

Hi all,

Basically I want the data in an open form to merge with a word
template and create a new document.

So a user navigates to a particular records and presses a button
"Produce Document" and access merges the current data with a template
and up pops a "save as" box allowing the user to save their new
document where ever.

There doesn't need to be any user options, or ability to add or create
templates - its just a merge to new document, same document every time
with the variables from the open form.

Can anyone help?

I'm an access novice on a steep learning curve so some 'on click' code
and module code would be wonderful if anyone has a solution!!

Thanks again!

[Salad]

Re: merge current form data to a new word file based on a word template

DeZZar wrote:

Hi all,
>
Basically I want the data in an open form to merge with a word
template and create a new document.
>
So a user navigates to a particular records and presses a button
"Produce Document" and access merges the current data with a template
and up pops a "save as" box allowing the user to save their new
document where ever.
>
There doesn't need to be any user options, or ability to add or create
templates - its just a merge to new document, same document every time
with the variables from the open form.
>
Can anyone help?
>
I'm an access novice on a steep learning curve so some 'on click' code
and module code would be wonderful if anyone has a solution!!
>
Thanks again!

Go to http://www.members.shaw.ca/AlbertKal.../msaccess.html and
scroll down/search for Super Easy Word Merge

[DeZZar]

Re: merge current form data to a new word file based on a wordtemplate

I've seen this and tried to make sense of it.....I think it is a litte
more than I need and hence complicates the process of trying to
implament it into my database.

I really do not want the features of being able to add new templates
or modifying existing ones. Just want some code the runs a pre
determined - never changing document. Record at a time.

The Kallal example that I downloaded was buggy and wouldn't open word
on my machine.

If anyone else can assist it would be a huge help!

Cheers

[DeZZar]

Re: merge current form data to a new word file based on a wordtemplate

actually the code supplied by Microsoft for an automated single merge
works well. I've pasted my working code below.

The only thing I would like to change about this code is that it
automatically prints the document to the default printer. I would
like my users to be able to save it instead - ie: 'save as' and then
select where they would like to put it.

is anyone able to change this code to save the document instead of
print??

(hope that text wrapping doesn't alter the code to much)

<CODE BEGINS HERE>
Private Sub MergeButton_Cli ck()
On Error GoTo MergeButton_Err
Dim objWord As Word.Applicatio n

' Remove the following comment in Microsoft Access 97.
' DoCmd.RunComman d acCmdCopy
' Remove the following comment in Microsoft Access 7.0.
' DoCmd.DoMenuIte m acFormBar, acEditMenu, acCopy, ,
acMenuVer70
' Start Microsoft Word 97.
Set objWord = CreateObject("W ord.Application ")
With objWord
' Make the application visible.
.Visible = True
' Open the document.
.Documents.Open ("G:\VIC\Lea se Lock Agreement.doc")
' Move to each bookmark and insert text from the form.
.ActiveDocument .Bookmarks("Cus tomerName").Sel ect
.Selection.Text = (CStr(Forms!frm _Leaselocks!
CustomerName))
.ActiveDocument .Bookmarks("ABN ").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!ABN ))
.ActiveDocument .Bookmarks("ACN _ARBN").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!ACN _ARBN))
.ActiveDocument .Bookmarks("Add ress").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Add ress))
.ActiveDocument .Bookmarks("Sta teCust").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Sta teCust))
.ActiveDocument .Bookmarks("Pos tcode").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Pos tcode))
.ActiveDocument .Bookmarks("Tru st").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Tru st))
.ActiveDocument .Bookmarks("Pro duct").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Pro duct))
.ActiveDocument .Bookmarks("Set tlementDate").S elect
.Selection.Text = (CStr(Forms!frm _Leaselocks!
SettlementDate) )
.ActiveDocument .Bookmarks("Ter m").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Ter m))
.ActiveDocument .Bookmarks("Fre quency").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Fre quency))
.ActiveDocument .Bookmarks("Ren tals").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Ren tals))
.ActiveDocument .Bookmarks("Amo unt").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Amo unt))
.ActiveDocument .Bookmarks("Bal loon_RV").Selec t
.Selection.Text = (CStr(Forms!frm _Leaselocks!Bal loon_RV))
.ActiveDocument .Bookmarks("Goo ds").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Goo ds))
.ActiveDocument .Bookmarks("Cus tomerRate").Sel ect
.Selection.Text = (CStr(Forms!frm _Leaselocks!
CustomerRate))
.ActiveDocument .Bookmarks("Set tlementDate2"). Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!
SettlementDate) )
End With
' Print the document in the foreground so Microsoft Word 97
' will not close until the document finishes printing.
objWord.ActiveD ocument.PrintOu t Background:=Fal se
' Close the document without saving changes.
objWord.ActiveD ocument.Close SaveChanges:=wd DoNotSaveChange s
' Quit Microsoft Word 97 and release the object variable.
objWord.Quit
Set objWord = Nothing
Exit Sub
MergeButton_Err :
' If a field on the form is empty
' remove the bookmark text and continue.
If Err.Number = 94 Then
objWord.Selecti on.Text = ""
Resume Next
' If the Photo field is empty.
ElseIf Err.Number = 2046 Then
MsgBox "Please add a photo to this record and try again."
Else
MsgBox Err.Number & vbCr & Err.Description
End If
Exit Sub
End Sub
<CODE ENDS HERE>

[Salad]

Re: merge current form data to a new word file based on a word template

DeZZar wrote:

actually the code supplied by Microsoft for an automated single merge
works well. I've pasted my working code below.
>
The only thing I would like to change about this code is that it
automatically prints the document to the default printer. I would
like my users to be able to save it instead - ie: 'save as' and then
select where they would like to put it.
>
is anyone able to change this code to save the document instead of
print??
>
(hope that text wrapping doesn't alter the code to much)
>
<CODE BEGINS HERE>
Private Sub MergeButton_Cli ck()
On Error GoTo MergeButton_Err
Dim objWord As Word.Applicatio n
>
' Remove the following comment in Microsoft Access 97.
' DoCmd.RunComman d acCmdCopy
' Remove the following comment in Microsoft Access 7.0.
' DoCmd.DoMenuIte m acFormBar, acEditMenu, acCopy, ,
acMenuVer70
' Start Microsoft Word 97.
Set objWord = CreateObject("W ord.Application ")
With objWord
' Make the application visible.
.Visible = True
' Open the document.
.Documents.Open ("G:\VIC\Lea se Lock Agreement.doc")
' Move to each bookmark and insert text from the form.
.ActiveDocument .Bookmarks("Cus tomerName").Sel ect
.Selection.Text = (CStr(Forms!frm _Leaselocks!
CustomerName))
.ActiveDocument .Bookmarks("ABN ").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!ABN ))
.ActiveDocument .Bookmarks("ACN _ARBN").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!ACN _ARBN))
.ActiveDocument .Bookmarks("Add ress").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Add ress))
.ActiveDocument .Bookmarks("Sta teCust").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Sta teCust))
.ActiveDocument .Bookmarks("Pos tcode").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Pos tcode))
.ActiveDocument .Bookmarks("Tru st").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Tru st))
.ActiveDocument .Bookmarks("Pro duct").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Pro duct))
.ActiveDocument .Bookmarks("Set tlementDate").S elect
.Selection.Text = (CStr(Forms!frm _Leaselocks!
SettlementDate) )
.ActiveDocument .Bookmarks("Ter m").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Ter m))
.ActiveDocument .Bookmarks("Fre quency").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Fre quency))
.ActiveDocument .Bookmarks("Ren tals").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Ren tals))
.ActiveDocument .Bookmarks("Amo unt").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Amo unt))
.ActiveDocument .Bookmarks("Bal loon_RV").Selec t
.Selection.Text = (CStr(Forms!frm _Leaselocks!Bal loon_RV))
.ActiveDocument .Bookmarks("Goo ds").Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!Goo ds))
.ActiveDocument .Bookmarks("Cus tomerRate").Sel ect
.Selection.Text = (CStr(Forms!frm _Leaselocks!
CustomerRate))
.ActiveDocument .Bookmarks("Set tlementDate2"). Select
.Selection.Text = (CStr(Forms!frm _Leaselocks!
SettlementDate) )
End With
' Print the document in the foreground so Microsoft Word 97
' will not close until the document finishes printing.
objWord.ActiveD ocument.PrintOu t Background:=Fal se
' Close the document without saving changes.
objWord.ActiveD ocument.Close SaveChanges:=wd DoNotSaveChange s
' Quit Microsoft Word 97 and release the object variable.
objWord.Quit
Set objWord = Nothing
Exit Sub
MergeButton_Err :
' If a field on the form is empty
' remove the bookmark text and continue.
If Err.Number = 94 Then
objWord.Selecti on.Text = ""
Resume Next
' If the Photo field is empty.
ElseIf Err.Number = 2046 Then
MsgBox "Please add a photo to this record and try again."
Else
MsgBox Err.Number & vbCr & Err.Description
End If
Exit Sub
End Sub
<CODE ENDS HERE>

Comment out this line to stop the printing
objWord.ActiveD ocument.PrintOu t Background:=Fal se

Maybe these ones as well
' Close the document without saving changes.
objWord.ActiveD ocument.Close SaveChanges:=wd DoNotSaveChange s
' Quit Microsoft Word 97 and release the object variable.
objWord.Quit

[DeZZar]

Re: merge current form data to a new word file based on a wordtemplate

Thanks Salad, this works well to just open the resultant document in
word and then you can save changes as normal!

The problem here is that it runs the risk of users overwriting the
source file template.

any other suggestions? Is there a way to instruct the code to
automatically bring up the 'save as' dialog in word?

[DeZZar]

Re: merge current form data to a new word file based on a wordtemplate

ok update: I've played a round with it a little and adding this code
now automatically saves the document with a file name taken from the
active form:

<CODE STARTS HERE>
objWord.ActiveD ocument.SaveAs filename:=(Form s!frm_Leaselock s!
CustomerName) & ".doc"

objWord.Quit

End With
<CODE ENDS HERE>

BUT!!.....it just saves to the default directory set in Word. Can
anyone tell me what to add to this code to change the directory I want
files saved automatically??

[Salad]

Re: merge current form data to a new word file based on a word template

DeZZar wrote:

Thanks Salad, this works well to just open the resultant document in
word and then you can save changes as normal!
>
The problem here is that it runs the risk of users overwriting the
source file template.
>
any other suggestions? Is there a way to instruct the code to
automatically bring up the 'save as' dialog in word?

For Word issues you may want to ask your questions in the
microsoft.publi c.word.vba
newsgroup. You'll find Word experts there.

[Salad]

Re: merge current form data to a new word file based on a word template

DeZZar wrote:

ok update: I've played a round with it a little and adding this code
now automatically saves the document with a file name taken from the
active form:
>
<CODE STARTS HERE>
objWord.ActiveD ocument.SaveAs filename:=(Form s!frm_Leaselock s!
CustomerName) & ".doc"
>
objWord.Quit
>
End With
<CODE ENDS HERE>
>
BUT!!.....it just saves to the default directory set in Word. Can
anyone tell me what to add to this code to change the directory I want
files saved automatically??

I suppose you could try this
Dim strTemplate As String
Dim strDoc as String

strTemplate = "C:\Templates\L easeLocks.doc"
strDoc = "C:\Documen ts\" & Forms!frm_Lease locks!CustomerN ame & ".doc"

Filecopy strTemplate, strDoc

and then modify the line to

.Documents.Open (strDoc)
and then do your merge on strDoc.

[DeZZar]

Re: merge current form data to a new word file based on a wordtemplate

thanks for all your help Salad!! This works really well!! For
anyone else that would like a similar solution here is the code that
worked for me!

<CODE STARTS HERE>

'SAVING THE LEASELOCK DOCUMENT TO SPECIFIC FOLDER
Private Sub MergeButton2_Cl ick()
On Error GoTo MergeButton2_Er r
Dim objWord As Word.Applicatio n

Dim strTemplate As String
Dim strDoc As String

'select location of the template file
strTemplate = "X:\<full path and file name for template>.doc"
'point word to save in the following directory. below takes directory
from form field
'form field set to concetenate information to form a directory path
strDoc = "X:\Leaselocks\ " & Forms!frm_Lease locks!GetDirect ory

FileCopy strTemplate, strDoc
Set objWord = CreateObject("W ord.Application ")
With objWord
.Visible = True
.Documents.Open (strDoc)

..ActiveDocumen t.Bookmarks("<n ame of your bookmark").Sele ct
..Selection.Tex t = (CStr(Forms!<yo ur form name>!<your field name>))
'repeat for each bookmark reference in your document

objWord.ActiveD ocument.Save

objWord.Quit

End With

Exit Sub
MergeButton2_Er r:
If Err.Number = 94 Then
objWord.Selecti on.Text = ""
Resume Next
Else
MsgBox Err.Number & vbCr & Err.Description
End If
Exit Sub
End Sub
<CODE ENDS HERE>