Merging Access Data to a Word Document

E

Elaine

I've created a Word document using a record's data in MS Access. I am using
Office 2007. I create the word and document objects using VBA, then using
the "documents.add", I open the .docx document. Using bookmarks, I insert my
Access data. Everything is working correctly, but what is happeningis that I
end up with two document, the one I'm using as the template and the document
I wish to create. Both show up. I've tried closing the original document
programatically and I've tried closing all documents, quitting Word, then
opening it again and the newly created document but I get an error that the
file is corrupt. I can open word separately and open the new document, but I
really need to have the document open when it's created. Can anyone help me
solve the problem so only the newly created document is open and the other
document is closed and unloaded. Thanks for the help. The following is the
VBA code I'm using in Access:

Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Add(sPath)

strsql = "Select * from [qIncDecRemLtr] where [RecID] =" & RecID.Value
Set db = Application.CurrentDb
Set rst = db.OpenRecordset(strsql)
rst.MoveFirst
sBusiness = rst.Fields("BusinessName")
With objWord
'Make the application visible.
.Visible = False
'Open the document.
.Documents.Add (sPath)
With objDoc
.Bookmarks("dDate").Range.Text = Format(rst.Fields("dDate"),
"mmmm dd, yyyy")
.Bookmarks("POC").Range.Text = Nz(rst.Fields("POC"))
.Bookmarks("Address1").Range.Text = Nz(rst.Fields("Address1"))
.Bookmarks("Address2").Range.Text = Nz(rst.Fields("Address2"))
.Bookmarks("C").Range.Text = Nz(rst.Fields("C"))
.Bookmarks("Acct").Range.Text = Nz(rst.Fields("Acct"))
.Bookmarks("BusinessName").Range.Text =
Nz(rst.Fields("BusinessName"))
.Bookmarks("CurrentDeposit").Range.Text =
Format(Nz(rst.Fields("CurrentDeposit")), "$#,###0.00")
.Bookmarks("Amt").Range.Text = Format(Nz(rst.Fields("Amt")),
"$#,###0.00")
If [RemainedSame].Value = 0 Then
.Bookmarks("NewDeposit").Range.Text =
Format(Nz(rst.Fields("DepRequired")), "$#,###0.00")
End If
End With
End With

objDoc.SaveAs CurrentProject.Path & "\Verification\IncDecRem- " &
sBusiness
objWord.Visible = True

rst.Close: Set rst = Nothing
db.Close: Set db = Nothing
 

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