Populate a Word document with a button?

[Tezcatlipoca]

Sorry if this isn't the right place for this, but it didn't seem to fit in anywhere else.

I have a simple database, which pretty much now works as I want it to. The only element I really needs to get working is some way of producing - at the click of a button - a Microsoft Word document, elements of which are populated by fields on whichever record the database user happens to be viewing at the time.

I already have the exact layout and template of the Word document I want, but I need to be able to view any record then click one button to fill that template with the record's fields and print it, and another button to fill the template with the record's fields and save it (again, in .doc format).

Is this possible?

 

[Ron_dK]

You might do a search on "Merge it with MS Word".

Hth

 

[Tezcatlipoca]

Thank you , Rak. Will get reading!

 

[The_Doc_Man]

I'll add that in Access, there are always (well, ... almost always) multiple ways to do certain things. In order of difficulty,

1. Let WORD do it. Uses minimal or no VBA programming.
a. Add a Yes/No field to your table.
b. Build a query that selects the cases where the Yes/No field is YES.
c. Build a Word document suitable for mailmerge based on that query.
d. Build a query that marks a record or set of records with YES and resets all other records, based on actions of your form.
e. Use the query wizard to kick off the MailMerge

2. ActiveX/Automation - requires VBA and understanding of COM operation
a. Open Word as an application
b. Create a new document
c. Start populating it. (This is where things get really tricky with formatting, and I don't recommend this if you have anything really special that has to look "just right" to be acceptable.)
d. Save and Close it.

If you can in any way use the Word/MailMerge path, take it. Less headaches, less worry, less time to develop. But it isn't the only way you can skin this particular cat. MMMMEEEEEOOOOOOWWWWWRRRRRR!
(And here you probably thought I liked cats....)

 

[g28dman]

Would this help...

I found this on this forum, maybe it will help. the code will save a doc template to a directory called word. I dont thinkk I have any read me files.

 

[Tezcatlipoca]

Thanks all,

I finally decided on - and succeeded with - setting up a Word template with bookmarks for the fields that were to be replaced.
A button was then added to the Access form, and the following 'onClick' code added:


Private Sub cmdSend_Click()

Dim WordApp As Word.Application
Dim strTemplateLocation As String

strTemplateLocation = "THE LOCATION OF MY TEMPLATE (MUST BE ABSOLUTE PATH, NOT RELATIVE)"


On Error Resume Next
Set WordApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set WordApp = CreateObject("Word.Application")
End If
On Error GoTo ErrHandler


WordApp.Visible = True
WordApp.WindowState = wdWindowStateMaximize
WordApp.Documents.Add Template:=strTemplateLocation, NewTemplate:=False

With WordApp.Selection

.Goto what:=wdGoToBookmark, Name:="bmrmanumber"
If IsNull(rmanumber) Then
.EndOf Unit:=wdParagraph, Extend:=wdExtend
.TypeBackspace
Else
.TypeText [rmanumber]
End If

.Goto what:=wdGoToBookmark, Name:="bmloggedon"
.TypeText [rmalogged]

.Goto what:=wdGoToBookmark, Name:="bmloggedby"
If IsNull(initials) Then
.EndOf Unit:=wdParagraph, Extend:=wdExtend
.TypeBackspace
Else
.TypeText [initials]
End If

.Goto what:=wdGoToBookmark, Name:="bmproduct"
If IsNull(producttype) Then
.EndOf Unit:=wdParagraph, Extend:=wdExtend
.TypeBackspace
Else
.TypeText [producttype]
End If

.Goto what:=wdGoToBookmark, Name:="bmpart"
If IsNull(partnumber) Then
.EndOf Unit:=wdParagraph, Extend:=wdExtend
.TypeBackspace
Else
.TypeText [partnumber]
End If

.Goto what:=wdGoToBookmark, Name:="bmwarrantyexpire"
.TypeText [warrantyexpires]

.Goto what:=wdGoToBookmark, Name:="bmfault"
If IsNull(fault) Then
.EndOf Unit:=wdParagraph, Extend:=wdExtend
.TypeBackspace
Else
.TypeText [fault]
End With

DoEvents
WordApp.Activate

Set WordApp = Nothing
Exit Sub

ErrHandler:
Set WordApp = Nothing

End Sub



This seems to work perfectly for me.

 

[Tezcatlipoca]

Ok, I've just hit a snag and would greatly appreciate some advice. The above code works perfectly for me, and opens up my template, replacing my pre-defiend bookmarks with the field data as required...

...however, I have just tried to embed my code into an existing database (the small database I'm writing is for testing only, I ultimately need to add this merging as a function of an exisiting office database), and the button I use to activate the merge code now results in a "User-defined type not defined" error, with a yellow highlight over the 'Private Sub cmdSend_Click()' line, and a black highlight over the 'Dim WordApp As Word.Application' line.

Now this works perfectly on the original database I've written, so it cannot - logically - be the code or the copy of Access at fault. I understand enough of the situation to get the idea that it must be some kind of variable in the database structure itself; something is different about the original database, where the code works, from the other database, where it doesn't, but I cannot for the life of me think what needs to be done.

Can anyone offer any advice?

 

[Tezcatlipoca]

Ah, found it. My references were not properly defined...doh! Works now.

 

[Tezcatlipoca]

Sorry, one more quick question. The function works exactly as I need it to now, with the following revised code:

Private Sub cmdSend_Click()

Dim WordApp As Word.Application
Dim strTemplateLocation As String

strTemplateLocation = "MY TEMPLATE LOCATION"


On Error Resume Next
Set WordApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set WordApp = CreateObject("Word.Application")
End If
On Error GoTo ErrHandler


WordApp.Visible = True
WordApp.WindowState = wdWindowStateMaximize
WordApp.Documents.Add Template:=strTemplateLocation, NewTemplate:=False

With WordApp.Selection

.GoTo what:=wdGoToBookmark, Name:="bmrmanumber"
.TypeText [rmanumber]

.GoTo what:=wdGoToBookmark, Name:="bmloggedon"
If IsNull(rmalogged) Then
.TypeText "N/A"
Else
.TypeText [rmalogged]
End If

.GoTo what:=wdGoToBookmark, Name:="bmloggedby"
If IsNull(initials) Then
.TypeText "N/A"
Else
.TypeText [initials]
End If

.GoTo what:=wdGoToBookmark, Name:="bmproduct"
If IsNull(producttype) Then
.TypeText "N/A"
Else
.TypeText [producttype]
End If

.GoTo what:=wdGoToBookmark, Name:="bmpart"
If IsNull(partnumber) Then
.TypeText "N/A"
Else
.TypeText [partnumber]
End If

.GoTo what:=wdGoToBookmark, Name:="bmwarrantexpire"
If IsNull(warrantyexpires) Then
.TypeText "N/A"
Else
.TypeText [warrantyexpires]
End If

.GoTo what:=wdGoToBookmark, Name:="bmcompany"
If IsNull(company) Then
.TypeText "N/A"
Else
.TypeText [company]
End If

.GoTo what:=wdGoToBookmark, Name:="bmcontact"
If IsNull(contactname) Then
.TypeText "N/A"
Else
.TypeText [contactname]
End If

.GoTo what:=wdGoToBookmark, Name:="bmfault"
If IsNull(fault) Then
.TypeText "N/A"
Else
.TypeText [fault]
End If

End With

DoEvents
WordApp.Activate

Set WordApp = Nothing
Exit Sub

ErrHandler:
Set WordApp = Nothing

End Sub


I just have one very minor issue which I'd like to know how to solve; viz. how do I set an automatic command for the opening document to automatically:

1) Name itself according to the "RMA_RMAnumber-Company.doc" format? I'm guessing I need some kind of SaveAs command, with the arguments being something like "RMA_" & me.rmanumber & "-" & me.company & ".doc", but don't actually know the proper command to impliment this, or where it should go in the code.

2) Not only name the document but have a copy save to a certain location as defined by an absolute path?


Any help you can give would be greatfully appreciated...

 

[Tezcatlipoca]

Sorry, but can anyone help with this final query, please?

I really, really, need to know how to have my script autosave the opening document to a certain folder, under the arguments "RMA_" WHATEVER IS IN THE RMANUMBER BOX "-" WHATEVER IS IN THE COMPANY BOX ".doc".

 

[JTB]

I think the line of code you need to save as is:

sav = "absolute path as a string"
app.ActiveDocument.SaveAs sav & "RMA_" & WHATEVER IS IN THE RMANUMBER BOX & "-" & WHATEVER IS IN THE COMPANY BOX & ".doc".

Hopefully that helps

 

[Tezcatlipoca]

I think the line of code you need to save as is:

sav = "absolute path as a string"
app.ActiveDocument.SaveAs sav & "RMA_" & WHATEVER IS IN THE RMANUMBER BOX & "-" & WHATEVER IS IN THE COMPANY BOX & ".doc".

Hopefully that helps

Thanks for getting abck to me, JTB, but I'm still running into trouble here.

If I modfy the top of my code to:

Dim WordApp As Word.Application
Dim strTemplateLocation As String
Dim strsav As String

strsav = "THE ABSOLUTE PATH OF THE FOLDER WHERE I WANT TO SAVE"

Then add the line:

app.ActiveDocument.SaveAs sav & "RMA_" & rmanumber & "-" & company & ".doc"

at the bottom, just above where I have:

DoEvents
WordApp.Activate

I just get 'variable not defined' errors on the command 'sav'. I have listed my vb code in an earlier post; how/where do I put these new lines?

 

[JTB]

sav is just a string variable that i used to store the absolute path. From your code, you're storing the absolute path in strsav so use that in the code at the bottom. Also, use WordApp instead of app:
WordApp.ActiveDocument.SaveAs strsav & "RMA_" & rmanumber & "-" & company & ".doc"

Hopefully that works for you.

 

[Tezcatlipoca]

Ah-ha! Ze saving, she works! Thank you, very much, JTB; your help is greatly appreciated.