Control Word from Access, use DocVariables


R

ryguy7272

I found this (below) Access code online:
Option Compare Database


Public Sub SendLetters()
Dim wapp As Word.Application
Dim wdoc As Word.Document
Dim fld As Word.FormField

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim filenm As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_CustomerData")
Set rs2 = db.OpenRecordset("tbl_MailInformation")

Set wapp = New Word.Application
wapp.Visible = True

rs.MoveFirst

While Not rs.EOF
If rs.Fields("Days_Past_Due").Value <= 30 Then
filenm = "C:\test\Letter1.doc"
End If
If rs.Fields("Days_Past_Due").Value > 30 Then
filenm = "C:\test\Letter2.doc"
End If

Set wdoc = wapp.Documents.Open(filenm)

For Each fld In wdoc.FormFields
fld.Result = rs.Fields(fld.Name).Value
Next fld

wdoc.SaveAs "C:\test\" & _
rs.Fields("Name").Value & ".doc"
wdoc.Close

rs2.AddNew
rs2.Fields("CustomerDataID").Value = rs.Fields("CustomerDataID").Value
rs2.Fields("DateMailed").Value = Now()
rs2.Fields("LetterMailed").Value = filenm
rs2.Update

Set wdoc = Nothing
rs.MoveNext
Wend

Set fld = Nothing
wapp.Quit
Set wapp = Nothing
rs2.Close
rs.Close
Set rs2 = Nothing
Set rs = Nothing
Set db = Nothing

End Sub


Works great! For me, the only problem is that the fields in the Word docs
are not named, so Access just pushes the data through. As far as I can tell,
the code doesn't really link certain fields from the Access table to certain
fields in the Word doc. I’ve done this before, pushing data from Excel to
Word, by using DocVariables in Word. There must be a similar way of doing
this in Access, by linking the fields in the Access table to the DocVariables
in the Word doc.

Below is a sample of the code that I use to control Word from Excel:
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)
'On Error Resume Next

objWord.ActiveDocument.variables("BrokerFirstName").Value =
Range("BrokerFirstName").Value
objWord.ActiveDocument.variables("BrokerLastName").Value =
Range("BrokerLastName").Value

objWord.ActiveDocument.Fields.Update

'On Error Resume Next
objWord.Visible = True

End Sub

When I try to run this code in Access I get this message:
Compile Error: Method or data member not found.

This is highlighted blue:
..GetOpenFilename

The reference to Word is checked off. What am I doing wrong?


Thanks,
Ryan---
 
Ad

Advertisements

A

Albert D. Kallal

I have a nice working sample that does a merge of the current record to
word.

The sample I have can be found here:
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

What is nice/interesting about my sample is that is specially designed to
enable ANY form with ONE LINE of code....

Thus, each time you build a new form, you can word merge enable it with
great ease.

Make sure you read the instructions from above, and you should eventually
get to the following page
http://www.members.shaw.ca/AlbertKallal/wordmerge/page2.html


Note that the merge can also use a query, and thus you don't have to merge
just "one" record..

After the merge occurs, you get a plain document WITHOUT any merge fields,
and this allows the end user to save, edit, or even email the document
(since the merge fields are gone after the merge occurs).

My solution also avoids hard to use bookmarks. And, when you create the word
document, you can use a drop-down list of fields to insert while "inside" of
word...and that drop down list of fields will be from your form.....

Give the above a try.
 
Ad

Advertisements

R

ryguy7272

Thanks for the info Albert!! I just glanced at the tool; very cool!! In the
meantime I found this:

Option Compare Database

Private Sub Command1_Click()
'Print customer slip for current customer.
Dim appWord As Word.Application
Dim doc As Word.Document
'Avoid error 429, when Word isn't open.
On Error Resume Next
Err.Clear
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'If Word isn't open, create a new instance of Word.
Set appWord = New Word.Application
End If
Set doc = appWord.Documents.Open("C:\test\CustomerSlip.doc", , True)
With doc
..FormFields("fldCustomerID").Result = Me!CustomerID
..FormFields("fldCompanyName").Result = Me!CompanyName
..FormFields("fldContactName").Result = Me!ContactName
..FormFields("fldContactTitle").Result = Me!ContactTitle
..FormFields("fldAddress").Result = Me!Address
..FormFields("fldCity").Result = Me!City
..FormFields("fldRegion").Result = Me!Region
..FormFields("fldPostalCode").Result = Me!PostalCode
..FormFields("fldCountry").Result = Me!Country
..FormFields("fldPhone").Result = Me!Phone
..FormFields("fldFax").Result = Me!Fax
..Visible = True
..Activate
End With
Set doc = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
End Sub

It uses Word Bookmarks instead of DocVariables, and I'm not a big fan of
Bookmarks, but I think I can live with this concept.

Anyone who sees this post should have a few good jumping off points now!!

Thanks again Albert!!

Regards,
Ryan---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top