By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

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

P: n/a
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!
Oct 30 '08 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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
Oct 30 '08 #2

P: n/a
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
Oct 30 '08 #3

P: n/a
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_Click()
On Error GoTo MergeButton_Err
Dim objWord As Word.Application

' Remove the following comment in Microsoft Access 97.
' DoCmd.RunCommand acCmdCopy
' Remove the following comment in Microsoft Access 7.0.
' DoCmd.DoMenuItem acFormBar, acEditMenu, acCopy, ,
acMenuVer70
' Start Microsoft Word 97.
Set objWord = CreateObject("Word.Application")
With objWord
' Make the application visible.
.Visible = True
' Open the document.
.Documents.Open ("G:\VIC\Lease Lock Agreement.doc")
' Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("CustomerName").Select
.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("Address").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Address))
.ActiveDocument.Bookmarks("StateCust").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!StateCust))
.ActiveDocument.Bookmarks("Postcode").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Postcode))
.ActiveDocument.Bookmarks("Trust").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Trust))
.ActiveDocument.Bookmarks("Product").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Product))
.ActiveDocument.Bookmarks("SettlementDate").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!
SettlementDate))
.ActiveDocument.Bookmarks("Term").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Term))
.ActiveDocument.Bookmarks("Frequency").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Frequency))
.ActiveDocument.Bookmarks("Rentals").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Rentals))
.ActiveDocument.Bookmarks("Amount").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Amount))
.ActiveDocument.Bookmarks("Balloon_RV").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Balloon_RV))
.ActiveDocument.Bookmarks("Goods").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Goods))
.ActiveDocument.Bookmarks("CustomerRate").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!
CustomerRate))
.ActiveDocument.Bookmarks("SettlementDate2").Selec t
.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.ActiveDocument.PrintOut Background:=False
' Close the document without saving changes.
objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
' 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.Selection.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>
Oct 30 '08 #4

P: n/a
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_Click()
On Error GoTo MergeButton_Err
Dim objWord As Word.Application

' Remove the following comment in Microsoft Access 97.
' DoCmd.RunCommand acCmdCopy
' Remove the following comment in Microsoft Access 7.0.
' DoCmd.DoMenuItem acFormBar, acEditMenu, acCopy, ,
acMenuVer70
' Start Microsoft Word 97.
Set objWord = CreateObject("Word.Application")
With objWord
' Make the application visible.
.Visible = True
' Open the document.
.Documents.Open ("G:\VIC\Lease Lock Agreement.doc")
' Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("CustomerName").Select
.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("Address").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Address))
.ActiveDocument.Bookmarks("StateCust").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!StateCust))
.ActiveDocument.Bookmarks("Postcode").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Postcode))
.ActiveDocument.Bookmarks("Trust").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Trust))
.ActiveDocument.Bookmarks("Product").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Product))
.ActiveDocument.Bookmarks("SettlementDate").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!
SettlementDate))
.ActiveDocument.Bookmarks("Term").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Term))
.ActiveDocument.Bookmarks("Frequency").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Frequency))
.ActiveDocument.Bookmarks("Rentals").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Rentals))
.ActiveDocument.Bookmarks("Amount").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Amount))
.ActiveDocument.Bookmarks("Balloon_RV").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Balloon_RV))
.ActiveDocument.Bookmarks("Goods").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!Goods))
.ActiveDocument.Bookmarks("CustomerRate").Select
.Selection.Text = (CStr(Forms!frm_Leaselocks!
CustomerRate))
.ActiveDocument.Bookmarks("SettlementDate2").Selec t
.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.ActiveDocument.PrintOut Background:=False
' Close the document without saving changes.
objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
' 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.Selection.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.ActiveDocument.PrintOut Background:=False

Maybe these ones as well
' Close the document without saving changes.
objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
' Quit Microsoft Word 97 and release the object variable.
objWord.Quit
Oct 30 '08 #5

P: n/a
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?
Oct 30 '08 #6

P: n/a
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.ActiveDocument.SaveAs filename:=(Forms!frm_Leaselocks!
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??
Oct 30 '08 #7

P: n/a
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.public.word.vba
newsgroup. You'll find Word experts there.

Oct 31 '08 #8

P: n/a
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.ActiveDocument.SaveAs filename:=(Forms!frm_Leaselocks!
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\LeaseLocks.doc"
strDoc = "C:\Documents\" & Forms!frm_Leaselocks!CustomerName & ".doc"

Filecopy strTemplate, strDoc

and then modify the line to

.Documents.Open (strDoc)
and then do your merge on strDoc.
Oct 31 '08 #9

P: n/a
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_Click()
On Error GoTo MergeButton2_Err
Dim objWord As Word.Application

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_Leaselocks!GetDirectory

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

..ActiveDocument.Bookmarks("<name of your bookmark").Select
..Selection.Text = (CStr(Forms!<your form name>!<your field name>))
'repeat for each bookmark reference in your document

objWord.ActiveDocument.Save

objWord.Quit

End With

Exit Sub
MergeButton2_Err:
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
Else
MsgBox Err.Number & vbCr & Err.Description
End If
Exit Sub
End Sub
<CODE ENDS HERE>
Nov 3 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.