Transfer data from Access to Word document

[RonNCmale]

I have a sample database in Access 2003 that I wish to have a command button that will open a word doc. and fill it with data from the database. I've searched through google and have not found any useful examples. I also wish the command button if the file is not found to open a find file command and save the path. Any help would be greatly appreaciated. Also I have several word documents to be filled with data from the database. Or point me in the right direction.

[SoftwareMatt]

You can do this 2 ways.

1. By doing a mailmerge in Word that links to a table or query and you can bring through any fields you require.
2. Use VBA to write data in to the Word document either straight in or using bookmarks.

[CreganTur]

Both mailmerge and writing to bookmarks work very well. Mail Merge is great if you want to send the contents of a query that contains multiple records to your word document. The bookmarks method is great if you only want to send over a single record to the document.

Let us know which one you want, and we'll provide some example code.

[RonNCmale]

Bookmarks since my database has no queries.

[CreganTur]

This code will actually create work letters using Bookmarks for every account in a query via a Loop, but you can adapt the code for your own uses.

You'll need to setup your document with bookmarks ahead of time.

Sub ExportQueryToWord()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim doc As Word.Document
Dim MyWord As Word.Application
Dim FirstName As String
Dim LastName As String
Dim fso As Object
Dim strFolder As String
'Check to see if folder exists
strFolder = '<<<Place the filepath to the folder that contains your word doc
Set fso = CreateObject("Scripting.FileSystemObject")
.create the folder if it doesn't exist
If Not fso.FolderExists(strFolder) Then
fso.CreateFolder (strFolder) '<<<Create folder if not exist
End If
Set db = CurrentDb '<<<Opens connection to current database
'create a connection to the query that shows the record you want to work with
Set rst = db.OpenRecordset("QueryName")
Set MyWord = New Word.Application
rst.MoveFirst '<<<Implicity move to first record in query
Do Until rst.EOF
'document that you want to push records to that contains bookmarks
Set doc = MyWord.Documents.Open("C:\TestDoc.doc")
MyWord.Visible = False '<<<works without showing the document
FirstName = rst!First_Name '<<<Grab first name from recordset as variable value
LastName = rst!Last_Name '<<<Same for last name
'paste variables into word document
doc.Bookmarks("FirstName").Range.Text = FirstName
doc.Bookmarks("LastName").Range.Text = LastName
'save and close document
doc.SaveAs (strFolder & "Name You Want Document To Have.doc")
doc.Close wdDoNotSaveChanges
rst.MoveNext
Loop
MsgBox "All letters have been created."
'quit word and release connections
MyWord.Quit
Set MyWord = Nothing
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End Sub

HTH:thumb

[RonNCmale]

Thanks Cregan, I'm not very good at codes, Can you modify this VBA for a record via loop instead of query. Also if filepath to word doc is not found a find file option and save. Thanks in advance

[RonNCmale]

an example would be helpful or location of one?

[CreganTur]

Can you modify this VBA for a record via loop instead of query.
If you don't use a query, then where are you getting your records from?


Also if filepath to word doc is not found a find file option and save.
The above code is setup so that if the folder filepath is not found, it will automatically create it. If you do not want to have a hardcoded filepath, but want the user to select it, then there are a few articles in the KB and plenty of posts about using the File Dialog window.

[RonNCmale]

The database only has a table right now. First Name, Last Name, City, State, Zip. It has 14 records of different individuals. I am trying to get a command button on the database that once activated would populate a word document of the current record showing in the database. Hopes this makes sense.

[CreganTur]

current record showing in the database.
Do you mean the current record showing on the form?

If so, you can control this with VBA in a number of ways. The easiest would be to pull the value of the form's objects by referring to them using the 'Me' keyword and the object's name. Example:
Dim FirstName As String
FirstName = Me.txtFirstName
where txtFirstName is the name of the textbox object on the form that holds the first name value from your table.

[RonNCmale]

Found a How to on TechRepublic "Fill Word form fields with Access data?" did exactly as they instructions indicated and getting "Compile error: user-defined type not defined". I checked the reference and it has a check mark in the DAO 3.X object library. I've been searching for an example to learn from. Any examples would be greatly appreaciated. :banghead:

[CreganTur]

Does the code require a reference to the Word object model?

[RonNCmale]

the procedure can be found here: http://blogs.techrepublic.com.com/msoffice/?p=164

[ZeidHaddad]

Hello! so i know i am kind of asking 10 years later but is this code instead of this one FirstName = rst!First_Name '<<<Grab first name from recordset as variable value
LastName = rst!Last_Name '<<<Same for last name
'paste variables into word document
doc.Bookmarks("FirstName").Range.Text = FirstName
doc.Bookmarks("LastName").Range.Text = LastName ?

[OBP]

I am not sure what your question is asking.
That code places the data from the firstname and lastname fields in the bookmarks with the same names as the fields.

[ZeidHaddad]

My Question is i am pulling data from a form not a query. so i need to change the code from

Set db = CurrentDb
Set rst = db.OpenRecordset("QueryName")

to something else correct ?
in other words i want to modify the whole code so i can adapt it on my form! is it possible ?

[OBP]

OK, now I understand.
When using a form instead of a Recordset you use
me.Fieldname
where fieldname is the actual name of the field on the form.

doc.Bookmarks("FirstName").Range.Text = me.FirstName

Although I use
doc.Bookmarks("FirstName").Text = me.FirstName

[ZeidHaddad]

great Thanks! i also need to change
Set db = CurrentDb
Set rst = db.OpenRecordset("Eignerbestand")

To something like

Set frm = CurrentForm
Set frm = DoCmd.OpenForm("Eignerbestand")

or i should keep it like this since its the record source

[ZeidHaddad]

Private Sub Befehl120_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim doc As Word.Document
Dim MyWord As Word.Application
Dim txtVorname As String
Dim txtName As String
Dim Anrede As String


Dim fso As Object
Dim strFolder As String


strFolder = "C:\Users\ve023g\Desktop\File"
Set fso = CreateObject("Scripting.FileSystemObject")


If Not fso.FolderExists(strFolder) Then
fso.CreateFolder (strFolder)
End If
Set frm = CurrentForm
Set frm = DoCmd.OpenForm("Eignerbestand")




Set MyWord = New Word.Application
rst.MoveFirst
Do Until rst.EOF
Set doc = MyWord.Documents.Open("C:\Users\ve023g\Desktop\File\zeid.docx")
MyWord.Visible = False
Vorname = rst!Vorname
Name = rst!Name


doc.Bookmarks("VorName").Range.Text = Me.txtV_Vorname
doc.Bookmarks("Name").Range.Text = Me.txtName
doc.Bookmarks("Anrede").Range.Text = Me.txtAnrede


doc.SaveAs (strFolder & "\NewBrief13.doc")
doc.Close wdDoNotSaveChanges
rst.MoveNext
Loop
MsgBox "All letters have been created."


MyWord.Quit
Set MyWord = Nothing
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing


End Sub


This is my code

[OBP]

You do not need to set anything to the form if it is already open, so those 2 lines of code and any lines closing or setting to nothing are also not needed.
It will only be open at 1 record so if you want to transfer more than one record to Word you either have loop through the Form records or Create a RecordsetClone and loop through that.
So the question is are you trying to transfer one record or all the records?